Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
# 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
# of worksheets limited by memory
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" ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum Number Of Worksheets In A Work Book-Not A Memory Issue???? | Excel Discussion (Misc queries) | |||
Memory hungry worksheets and workbooks | Excel Worksheet Functions | |||
Increasing memory/no of worksheets in excel | Excel Discussion (Misc queries) | |||
Is the list of Worksheets limited to "This Workbook" | Excel Programming | |||
Q: Best way to take data from VBA into graphs without writing data to worksheets? (Can a named range refer to an array in memory only?) | Excel Programming |