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)


--
Regards
Frank Kabel
Frankfurt, Germany


Karl Burrows wrote:
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),(Co
unt),
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!!!!