# of worksheets limited by memory

Hello fellow helpers,

When I query Excel help as to the limit of worksheets in a workbook I get
the somewhat cryptic message that worksheets are limited by available memory.
Has anyone experienced any limitations on the number of worksheets? I am
trying to answer this question prior to coding a workbook with the
possibility of over 200 worksheets.

Thanks,
Nicole

Only bounded by memeory. Will your workbook crash with 200+ worksheets.
Depends how much stuff is on them. Here is a link to memory limits in Excel.

http://www.decisionmodels.com/memlimits.htm

My question to you would be what are you trying to do that is going to
require 200 worksheets? With that many sheets will the spreadsheet be usable?
--
HTH...

Jim Thomlinson


"Nicole Seibert" wrote:

> Hello fellow helpers,
>
> When I query Excel help as to the limit of worksheets in a workbook I get
> the somewhat cryptic message that worksheets are limited by available memory.
> Has anyone experienced any limitations on the number of worksheets? I am
> trying to answer this question prior to coding a workbook with the
> possibility of over 200 worksheets.
>
> Thanks,
> Nicole

Good question. I am automating the creation of reports spaning three
worksheet per manager. One worksheet houses definitions and will be pulled
from elsewhere. The first two worksheets will include sensitive information
for their eyes only and hence needs to be separated from other manager's
information. Also, we need to keep these records for a year-and-a-half.
There are over two hundred managers, but depending on the information not all
managers will receive a report; I am guesstamating that there will be at
least 200 sheets.

Thanks,
Nicole

"Jim Thomlinson" wrote:

> Only bounded by memeory. Will your workbook crash with 200+ worksheets.
> Depends how much stuff is on them. Here is a link to memory limits in Excel.
>
> http://www.decisionmodels.com/memlimits.htm
>
> My question to you would be what are you trying to do that is going to
> require 200 worksheets? With that many sheets will the spreadsheet be usable?
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Nicole Seibert" wrote:
>
> > Hello fellow helpers,
> >
> > When I query Excel help as to the limit of worksheets in a workbook I get
> > the somewhat cryptic message that worksheets are limited by available memory.
> > Has anyone experienced any limitations on the number of worksheets? I am
> > trying to answer this question prior to coding a workbook with the
> > possibility of over 200 worksheets.
> >
> > Thanks,
> > Nicole

Since I don't know a whole pile about whate exactly you need I will give a
very general response... When creating the type of spreadsheet that you are
describing usually you are best off to separte the spreadsheet into layers.

One layer just holds data. The only person who ever sees it is you (the
developer) This will be a small handfull of sheets that contain the data for
all of the managers. The data is all kept in one place and there are few if
any calculations on these sheets. This is refered to as the data layer.

The next layer is the Business Logic Layer (this layer may or may not be
required). Lets say you have your source data on a few different data sheets.
Here you will do some of your calculations to manipulte and relate the data
from the different data sources to calculate final values that you wnat to
show to the end users. Normally this will be one sheet (or just a couple).

The final layer is the User Interface. This layer grabs information from
your Business logic layer and displays it to the end user. Usually it is a
bunch of Vlookups on sumproduct formulas. Once again it should only be a
handfull of sheets. It needs to be interactive to allow the user to select
each manager individually. Pivot Tables are also great for doing this...

The nice thing about doing it this way is that it will only be a hand full
of sheets (as opposed to 200+). If changes are required you can easily modify
it. If someone decides that they want a new report it is usually no big
deal.

--
HTH...

Jim Thomlinson


"Nicole Seibert" wrote:

> Good question. I am automating the creation of reports spaning three
> worksheet per manager. One worksheet houses definitions and will be pulled
> from elsewhere. The first two worksheets will include sensitive information
> for their eyes only and hence needs to be separated from other manager's
> information. Also, we need to keep these records for a year-and-a-half.
> There are over two hundred managers, but depending on the information not all
> managers will receive a report; I am guesstamating that there will be at
> least 200 sheets.
>
> Thanks,
> Nicole
>
> "Jim Thomlinson" wrote:
>
> > Only bounded by memeory. Will your workbook crash with 200+ worksheets.
> > Depends how much stuff is on them. Here is a link to memory limits in Excel.
> >
> > http://www.decisionmodels.com/memlimits.htm
> >
> > My question to you would be what are you trying to do that is going to
> > require 200 worksheets? With that many sheets will the spreadsheet be usable?
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Nicole Seibert" wrote:
> >
> > > Hello fellow helpers,
> > >
> > > When I query Excel help as to the limit of worksheets in a workbook I get
> > > the somewhat cryptic message that worksheets are limited by available memory.
> > > Has anyone experienced any limitations on the number of worksheets? I am
> > > trying to answer this question prior to coding a workbook with the
> > > possibility of over 200 worksheets.
> > >
> > > Thanks,
> > > Nicole

Nicole Seibert wrote:
> Hello fellow helpers,
>
> When I query Excel help as to the limit of worksheets in a workbook I get
> the somewhat cryptic message that worksheets are limited by available memory.
> Has anyone experienced any limitations on the number of worksheets? I am
> trying to answer this question prior to coding a workbook with the
> possibility of over 200 worksheets.
>
> Thanks,
> Nicole

Nicole,

You are creating a monster. I can almost guarantee that your WB will
be EXTREMELY large, EXTREMELY slow to calculate, as well as prone to
corruption and causing excel to crash. I would recommend you store all
of your data in access or another database and program excel to pull
out the data the data that actually needs to be looked at. surely your
users aren't going to review all 200 sheets. Generally speaking, i've
found that if your spreadsheet is greater than 3-5 MB in size, you
should be working with a database.... Hope this helps

AR

I have already created the data layer downloading data. I am still waiting
on a requirements doc, but ...

Two or three additional bits of information that may throw a wrench in it:
1. each set of three worksheets will be emailed to the manager
2. and due to its senstive nature I can not show any manager another manager
data.
3. There will be a bar graph on each sheet which I am assuming will take up
more memory than interactive sheets.

My current work around is to possible create several groups of data. I
would then close down sections of data and open new ones in sucession. Based
on my data it would make sence to do it in three groups.

Oh and I am using Windows 2000/NT with Excel 2003. I used your macro and
created 400 interactive sheets with only a minor glitch in that they were not
in order. Is this normal?

Any other ideas would be helpful.

Thanks,
Nicole


"Jim Thomlinson" wrote:

> Since I don't know a whole pile about whate exactly you need I will give a
> very general response... When creating the type of spreadsheet that you are
> describing usually you are best off to separte the spreadsheet into layers.
>
> One layer just holds data. The only person who ever sees it is you (the
> developer) This will be a small handfull of sheets that contain the data for
> all of the managers. The data is all kept in one place and there are few if
> any calculations on these sheets. This is refered to as the data layer.
>
> The next layer is the Business Logic Layer (this layer may or may not be
> required). Lets say you have your source data on a few different data sheets.
> Here you will do some of your calculations to manipulte and relate the data
> from the different data sources to calculate final values that you wnat to
> show to the end users. Normally this will be one sheet (or just a couple).
>
> The final layer is the User Interface. This layer grabs information from
> your Business logic layer and displays it to the end user. Usually it is a
> bunch of Vlookups on sumproduct formulas. Once again it should only be a
> handfull of sheets. It needs to be interactive to allow the user to select
> each manager individually. Pivot Tables are also great for doing this...
>
> The nice thing about doing it this way is that it will only be a hand full
> of sheets (as opposed to 200+). If changes are required you can easily modify
> it. If someone decides that they want a new report it is usually no big
> deal.
>
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Nicole Seibert" wrote:
>
> > Good question. I am automating the creation of reports spaning three
> > worksheet per manager. One worksheet houses definitions and will be pulled
> > from elsewhere. The first two worksheets will include sensitive information
> > for their eyes only and hence needs to be separated from other manager's
> > information. Also, we need to keep these records for a year-and-a-half.
> > There are over two hundred managers, but depending on the information not all
> > managers will receive a report; I am guesstamating that there will be at
> > least 200 sheets.
> >
> > Thanks,
> > Nicole
> >
> > "Jim Thomlinson" wrote:
> >
> > > Only bounded by memeory. Will your workbook crash with 200+ worksheets.
> > > Depends how much stuff is on them. Here is a link to memory limits in Excel.
> > >
> > > http://www.decisionmodels.com/memlimits.htm
> > >
> > > My question to you would be what are you trying to do that is going to
> > > require 200 worksheets? With that many sheets will the spreadsheet be usable?
> > > --
> > > HTH...
> > >
> > > Jim Thomlinson
> > >
> > >
> > > "Nicole Seibert" wrote:
> > >
> > > > Hello fellow helpers,
> > > >
> > > > When I query Excel help as to the limit of worksheets in a workbook I get
> > > > the somewhat cryptic message that worksheets are limited by available memory.
> > > > Has anyone experienced any limitations on the number of worksheets? I am
> > > > trying to answer this question prior to coding a workbook with the
> > > > possibility of over 200 worksheets.
> > > >
> > > > Thanks,
> > > > Nicole

then you should create separate workbooks that contain the information for
each manager. You still might be able to use Jim's suggestion and create
these sheets dynamically by using a pivot table and pivot chart. then copy
the sheet(s) to a new workbook and make them static (cell.copy then
cells.pastespecial xlvalues on each sheet). Then mail that special workbook
to a manager, then process the next one the same way - changing the values
that control the information displayed in the pivottables. It didn't sound
like you needed an interactive portion for the manager.

--
Regards,
Tom Ogilvy


"Nicole Seibert" wrote:

> I have already created the data layer downloading data. I am still waiting
> on a requirements doc, but ...
>
> Two or three additional bits of information that may throw a wrench in it:
> 1. each set of three worksheets will be emailed to the manager
> 2. and due to its senstive nature I can not show any manager another manager
> data.
> 3. There will be a bar graph on each sheet which I am assuming will take up
> more memory than interactive sheets.
>
> My current work around is to possible create several groups of data. I
> would then close down sections of data and open new ones in sucession. Based
> on my data it would make sence to do it in three groups.
>
> Oh and I am using Windows 2000/NT with Excel 2003. I used your macro and
> created 400 interactive sheets with only a minor glitch in that they were not
> in order. Is this normal?
>
> Any other ideas would be helpful.
>
> Thanks,
> Nicole
>
>
> "Jim Thomlinson" wrote:
>
> > Since I don't know a whole pile about whate exactly you need I will give a
> > very general response... When creating the type of spreadsheet that you are
> > describing usually you are best off to separte the spreadsheet into layers.
> >
> > One layer just holds data. The only person who ever sees it is you (the
> > developer) This will be a small handfull of sheets that contain the data for
> > all of the managers. The data is all kept in one place and there are few if
> > any calculations on these sheets. This is refered to as the data layer.
> >
> > The next layer is the Business Logic Layer (this layer may or may not be
> > required). Lets say you have your source data on a few different data sheets.
> > Here you will do some of your calculations to manipulte and relate the data
> > from the different data sources to calculate final values that you wnat to
> > show to the end users. Normally this will be one sheet (or just a couple).
> >
> > The final layer is the User Interface. This layer grabs information from
> > your Business logic layer and displays it to the end user. Usually it is a
> > bunch of Vlookups on sumproduct formulas. Once again it should only be a
> > handfull of sheets. It needs to be interactive to allow the user to select
> > each manager individually. Pivot Tables are also great for doing this...
> >
> > The nice thing about doing it this way is that it will only be a hand full
> > of sheets (as opposed to 200+). If changes are required you can easily modify
> > it. If someone decides that they want a new report it is usually no big
> > deal.
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Nicole Seibert" wrote:
> >
> > > Good question. I am automating the creation of reports spaning three
> > > worksheet per manager. One worksheet houses definitions and will be pulled
> > > from elsewhere. The first two worksheets will include sensitive information
> > > for their eyes only and hence needs to be separated from other manager's
> > > information. Also, we need to keep these records for a year-and-a-half.
> > > There are over two hundred managers, but depending on the information not all
> > > managers will receive a report; I am guesstamating that there will be at
> > > least 200 sheets.
> > >
> > > Thanks,
> > > Nicole
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Only bounded by memeory. Will your workbook crash with 200+ worksheets.
> > > > Depends how much stuff is on them. Here is a link to memory limits in Excel.
> > > >
> > > > http://www.decisionmodels.com/memlimits.htm
> > > >
> > > > My question to you would be what are you trying to do that is going to
> > > > require 200 worksheets? With that many sheets will the spreadsheet be usable?
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Nicole Seibert" wrote:
> > > >
> > > > > Hello fellow helpers,
> > > > >
> > > > > When I query Excel help as to the limit of worksheets in a workbook I get
> > > > > the somewhat cryptic message that worksheets are limited by available memory.
> > > > > Has anyone experienced any limitations on the number of worksheets? I am
> > > > > trying to answer this question prior to coding a workbook with the
> > > > > possibility of over 200 worksheets.
> > > > >
> > > > > Thanks,
> > > > > Nicole

I have done almost the exact thing that you are proposing. My solution was to
have my main spreadsheet create a seperate workbook for each manager
(calculated commission in a heirarchy structure). I then just sent them their
workbook. I had code that looped through the list of managers and created a
new workbook for each, containing only the their sheets. It worked like a
charm. I am not saying don't follow your current path, I am just saying from
experience that it is problematic and not very flexible.

I have seen the quality of your posts and I have an idea of your experience
level. I would not recommend this project for everyone but it is well within
your abilities. And heck we are always here to get you through the sticky
parts.
--
HTH...

Jim Thomlinson


"Nicole Seibert" wrote:

> I have already created the data layer downloading data. I am still waiting
> on a requirements doc, but ...
>
> Two or three additional bits of information that may throw a wrench in it:
> 1. each set of three worksheets will be emailed to the manager
> 2. and due to its senstive nature I can not show any manager another manager
> data.
> 3. There will be a bar graph on each sheet which I am assuming will take up
> more memory than interactive sheets.
>
> My current work around is to possible create several groups of data. I
> would then close down sections of data and open new ones in sucession. Based
> on my data it would make sence to do it in three groups.
>
> Oh and I am using Windows 2000/NT with Excel 2003. I used your macro and
> created 400 interactive sheets with only a minor glitch in that they were not
> in order. Is this normal?
>
> Any other ideas would be helpful.
>
> Thanks,
> Nicole
>
>
> "Jim Thomlinson" wrote:
>
> > Since I don't know a whole pile about whate exactly you need I will give a
> > very general response... When creating the type of spreadsheet that you are
> > describing usually you are best off to separte the spreadsheet into layers.
> >
> > One layer just holds data. The only person who ever sees it is you (the
> > developer) This will be a small handfull of sheets that contain the data for
> > all of the managers. The data is all kept in one place and there are few if
> > any calculations on these sheets. This is refered to as the data layer.
> >
> > The next layer is the Business Logic Layer (this layer may or may not be
> > required). Lets say you have your source data on a few different data sheets.
> > Here you will do some of your calculations to manipulte and relate the data
> > from the different data sources to calculate final values that you wnat to
> > show to the end users. Normally this will be one sheet (or just a couple).
> >
> > The final layer is the User Interface. This layer grabs information from
> > your Business logic layer and displays it to the end user. Usually it is a
> > bunch of Vlookups on sumproduct formulas. Once again it should only be a
> > handfull of sheets. It needs to be interactive to allow the user to select
> > each manager individually. Pivot Tables are also great for doing this...
> >
> > The nice thing about doing it this way is that it will only be a hand full
> > of sheets (as opposed to 200+). If changes are required you can easily modify
> > it. If someone decides that they want a new report it is usually no big
> > deal.
> >
> > --
> > HTH...
> >
> > Jim Thomlinson
> >
> >
> > "Nicole Seibert" wrote:
> >
> > > Good question. I am automating the creation of reports spaning three
> > > worksheet per manager. One worksheet houses definitions and will be pulled
> > > from elsewhere. The first two worksheets will include sensitive information
> > > for their eyes only and hence needs to be separated from other manager's
> > > information. Also, we need to keep these records for a year-and-a-half.
> > > There are over two hundred managers, but depending on the information not all
> > > managers will receive a report; I am guesstamating that there will be at
> > > least 200 sheets.
> > >
> > > Thanks,
> > > Nicole
> > >
> > > "Jim Thomlinson" wrote:
> > >
> > > > Only bounded by memeory. Will your workbook crash with 200+ worksheets.
> > > > Depends how much stuff is on them. Here is a link to memory limits in Excel.
> > > >
> > > > http://www.decisionmodels.com/memlimits.htm
> > > >
> > > > My question to you would be what are you trying to do that is going to
> > > > require 200 worksheets? With that many sheets will the spreadsheet be usable?
> > > > --
> > > > HTH...
> > > >
> > > > Jim Thomlinson
> > > >
> > > >
> > > > "Nicole Seibert" wrote:
> > > >
> > > > > Hello fellow helpers,
> > > > >
> > > > > When I query Excel help as to the limit of worksheets in a workbook I get
> > > > > the somewhat cryptic message that worksheets are limited by available memory.
> > > > > Has anyone experienced any limitations on the number of worksheets? I am
> > > > > trying to answer this question prior to coding a workbook with the
> > > > > possibility of over 200 worksheets.
> > > > >
> > > > > Thanks,
> > > > > Nicole

Nicole,
In addition to Jim's advice, you are aware that Excel's security can be
overridden in a matter of seconds.
If you need to keep jealous, rival managers from seeing each others figures,
Excel is probably not the correct solution.
Or arrange it such a way the each file only data for a single manger.

Or use a DB for the data and only use Excel for the reporting.

NickHK


"Nicole Seibert" <[email protected]> ¼¶¼g©ó¶l¥ó·s»D:[email protected]...
> Good question. I am automating the creation of reports spaning three
> worksheet per manager. One worksheet houses definitions and will be
> pulled
> from elsewhere. The first two worksheets will include sensitive
> information
> for their eyes only and hence needs to be separated from other manager's
> information. Also, we need to keep these records for a year-and-a-half.
> There are over two hundred managers, but depending on the information not
> all
> managers will receive a report; I am guesstamating that there will be at
> least 200 sheets.
>
> Thanks,
> Nicole
>
> "Jim Thomlinson" wrote:
>
>> Only bounded by memeory. Will your workbook crash with 200+ worksheets.
>> Depends how much stuff is on them. Here is a link to memory limits in
>> Excel.
>>
>> http://www.decisionmodels.com/memlimits.htm
>>
>> My question to you would be what are you trying to do that is going to
>> require 200 worksheets? With that many sheets will the spreadsheet be
>> usable?
>> --
>> HTH...
>>
>> Jim Thomlinson
>>
>>
>> "Nicole Seibert" wrote:
>>
>> > Hello fellow helpers,
>> >
>> > When I query Excel help as to the limit of worksheets in a workbook I
>> > get
>> > the somewhat cryptic message that worksheets are limited by available
>> > memory.
>> > Has anyone experienced any limitations on the number of worksheets? I
>> > am
>> > trying to answer this question prior to coding a workbook with the
>> > possibility of over 200 worksheets.
>> >
>> > Thanks,
>> > Nicole