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!!!!