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

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

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

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

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

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.

"Frank Kabel" wrote in message
...
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!!!!

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

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

"Karl Burrows" wrote in message
.. .
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!!!!

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

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

"Karl Burrows" wrote in message . ..
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.

"Frank Kabel" wrote in message
...
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!!!!





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004