Lots of Frustration - Lots of Arrays, Dynamic Ranges Don't Work, Help With Options

[Karl Burrows]

Okay, I have posted many times over the past few years about these darn
spreadsheets. I appreciate all the help in the past. I picked up a few
things here and there, but I just can't get over the hump and fix these
worksheets so I don't have to keep managing them. I will go into detail
below, but I have just way too many calculations in these workbooks. Some
workbooks are as large as 10MB and have thousands of formulas, so they can
be very cumbersome. I have tried named dynamic ranges, tried to simplify
the formula process and removed as much 'bloat' (like hyperlinks to navigate
around the workbooks, formatting, etc.), but I still have not figured out a
way to make these run without still having to monitor the data to make sure
it stays within my defined ranges, etc. There just has to be a way to make
this work as the database grows.

Here it goes!...

Overall Setup - I have several workbooks that track sales for lots and homes
in neighborhoods. Each workbook includes a data page that pulls data from
an Access database where we input all of our sales information. The other
tabs in the workbooks track each neighborhood separately by builder with a
summary tab at the beginning that 'rolls up' all of our data into an overall
performance page.

Data - The data retrieved from Access includes the Neighborhood name, the
builder, the lot number, price and the closing date of the lot. I added a
row at the end to 'count' sales for the arrays in the worksheets. Right now
there are about 2000 rows of data that populate this page. I created named
range for each column that looks at 2500 rows to give me some fluff. Here
is one of my major dilemmas. I have to keep an eye on the data to make sure
it doesn't exceed 2500 rows or the worksheets will be incomplete (Some
neighborhoods move off and are replaced with new ones, so I edit the query
to not import data for neighborhoods we no longer need to track). I tried
creating dynamic named ranges, but because they use the OFFSET formula, it
freezes the workbook since there are so many calculations that use the
OFFSET named range. Here's the formula (one for each column of the data
tab):

Subdivision Named Range:
=OFFSET(Database!$B$3,0,0,COUNTA(Database!$B:$B),1)

Question 1...How can I use OFFSET or another formula to keep a dynamic range
without overloading the workbooks.

Question 2...If I create a new data query in the existing workbook, the
formulas in the arrays I will describe below change to reflect the new range
of data (rows 3:1955 for example) instead of the 3:2503 I created manually.
If I just left this formula alone, will it change dynamically by itself to
redefine the new range of data that may get imported the next time I refresh
the data or open the workbook (ex. I open the worksheet the next time and
there have been 50 new lots added, will the formula automatically change
from rows 3:1955 to 3:2005)?

Neighborhood worksheets - Each worksheet details sales in that neighborhood
by builder by date for a period of 5 years (right now it is 2001 to 2006).
There are up to 5 builders in each neighborhood. Each month has an array
formula that looks at the subdivision name on that tab, the builder and the
date.

{=SUM(IF((Subdivision=$B$3)*(Builder=$B31)*(Date>=E$9)*(Date<=E$11),(Count),
0))}

Date above reflects a search for a date within the first and last day of the
month. Count is the extra column on the data page to give the total lots
(Basically puts a '1' next to the data).

So, if I do some simple math, there are 5 builders per worksheet for 5 years
or 300 array formulas per page. There are 18-20 pages per workbook, so
there are a total of somewhere close to 6000 formulas!!! There are a few
more array formulas stuck in a few places to find lot sales prior to the Jan
2001 date I started with so I have grand totals, etc. They add another 100
array formulas.

Question 3...Is there a more efficient way to find data other than SUMIF
based on how I need to pull the data into the worksheets?

Question 4...Is there a way to not calculate the formulas on any of the
worksheets that are not in use? I keep several spare worksheets for when we
add new neighborhoods, so they are calculating and don't need to. They are
named New1, New2, etc.

I mentioned there are several similar workbooks. Well, we track lot sales
in one workbook. There is an almost identical workbook that track home
sales on those lots. The rest of the workbooks track summary information
that relate to the neighborhoods and builders. What I have done is the main
setup goes in the lot workbook I have described above and all the other
workbooks link to the this main workbook. So, if neighborhood name is
changed, builders change, etc. it is updated in all the other workbooks. My
problem here is if I need to change a neighborhood and want to rename the
tab (I have assigned a 3 or 4 letter code for each neighborhood), I have to
have all the workbooks open at the same time to rename the tab or I get #REF
errors when I open the other workbooks and the initial worksheet has been
renamed (yes, I know I get prompted for the missing workbook tab to keep the
link, but the worksheets are protected so all these formulas don't get
overwritten, so the links will not update and I'm not there all the time to
do it, so I tell them it is better to open them all than have to fix all the
mess).

Question 5...How can I create a Macro to replicate the tab names and keep
the links throughout all the workbooks without having to open all of them at
the same time? There are 6 workbooks that are all linked. Is there a way
to create a tab name list somewhere in the main workbook that defines the
name of the tabs for all other related workbooks? I know one issue is sheet
numbers in VBA. These sheets will get moved around as they like to
rearrange the tabs to put them in alpha order (BOP, BLG, KNG, WGV for
example and if we added MLA, they would want to move it between KNG and
WGV).

I know this is a lot, but I have been beating myself up for several years
trying to figure out how to get these few items to work. I know many will
recommend a Pivot Table. Unfortunately, there is not enough Excel knowledge
with the people that use these workbooks to try that. In addition, the
sales tables are linked to Word docs for monthly status reports, so a Pivot
Table would not work, as I would have to manually create new tables and
links each month. Others will recommend keeping the monthly rolling
reporting in Access. I would like to do this myself, but I don't think
Access offers the flexibility to change the date ranges on the fly, create
the columnar data in the format they want, link tables to Word docs, etc.
(as an example, they don't want a neighbor to start populating a worksheet
until it has actual started, so I have created formulas to look at a start
date to begin filling in cells). I'll live with slower workbooks to keep
the formatting they want.

Thank you for taking the time to read this. if you have any suggestions, I
am all ears. I spend too much time fixing their problems just from what
errors they input into the database and wrong information in the worksheet
setup. I sure could use a break on watching the workbooks to make sure I
keep data ranges correct and listening to them complain about how slow they
are!

Thanks!!!!

 

[Frank Kabel]

Hi Karl
not a solution but looking at your description I think you already have
the right conclusions:
1. Excel is not well suited for this task.
- You have this problem with dynamic ranges (esp. with so many
worksheets in use)
- Consistency is not ensured by Excel's build-in mechanism
- The workbooks get slow

2. Putting this in a databse like application would be much better. Use
Excel only for reporting purpose. Also use Pivot tables for reporting
(Take the time to train the users -> IMHO they will like the
flexibility). Link the pivot tables directly to your databse (e.g. MS
Access)

3. For Word outputs also link directly to the database and not to MS
Access

4. Yes changing some things are a little bit more complicated in Access
but you have the benefits of
-> more consistency
-> probably faster
-> sharing data is also better supported
-> User can't mess your data structure so easily


So IMHO I'd try to re-implement your solution (knowing this will be
painful)

 

[BrianB]

This seems to be an example of "starting in the wrong place" - which w
all do to start with. It may be time to "bite the bullet" and star
again.

The basic foundation for data should be a simple table with headings a
the top and rows of data underneath. I call this the "databas
approach". Any new data gets added to the bottom, so some sort of Dat
column will probably be needed.

Having got this, the more powerful functions of Excel can be used, a
well as pivot tables, sorting and filters, to produce the require
extracts and reports. I am sure that you will see how this will solv
many of the problems you mention.

To begin with, it is a simple decision, do you continue doing what yo
are doing - or do you really need to change. Yes, your colleagues ma
have to go through a new learning curve too, but if the work i
simplified the macros will be too

 

[Karl Burrows]

Thanks for all the input and assistance with this (including past posts!).
1 & 2 are right on the money. #3 is kind of hard. These reports are seen
by many people and they have certain formatting they follow for each
document. By only bringing the data from Access would probably require as
much programming as just doing the whole thing there in the first place.
That is my biggest hang-up. I don't think they care how the data is
formatted in the workbooks, but the reporting is more critical, which is why
it was done in Excel to begin with.

I appreciate the input. Maybe I'll find an intermediate solution until I
can run it in Access.

 

[Karl Burrows]

Yes, I agree. Unfortunately, I created it years ago as a way to get data
quickly from Access. They had no way to report results without getting out
all these odd worksheets and adding them up on a calculator. I did this to
get them an easy way to extract the data. Over the years, it has grown as
they realized how poorly they tracked their info and also realized the
benefits of centralized data management.

 

[Charles Williams]

Hi Karl,

If you can send me a small version (1 data sheet and 1 formula worksheet) in
a zipped workbook, I will try to show you how to fix this problem.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Robert McCurdy]

Fancy favouring Access on an Excel NG Frank!
Are you trying whittle down the XL user base?

Charles Williams is very good, so follow his advice

My 2c worth is your problem can be reduced with a little bit more organising. Try and see what the end user needs to do their work.
And what you need to see to be happy with the result.
I think you should consider smaller workbooks and fewer spreadsheets.
And finally, checkout Advance filter, XL's most under rated feature. With this set up right you could create any report in just a
few steps.


Regards Robert