Estimate number of worksheets
If the number of worksheets in an Excel workbook is limited by the available memory, is there a way to calculate an estimated number of worksheets that can be created?
For example, if 64 bit, w7 system had 1.2 of "available memory", about how many worksheets would be able to be created "without" formulas or pivot tables - just plain data?
Also, if this data was manipulated in Access and exported to Excel would that affect the number of worksheets in Excel? If so, up or down?
For example, if 64 bit, w7 system had 1.2 of "available memory", about how many worksheets would be able to be created "without" formulas or pivot tables - just plain data?
Also, if this data was manipulated in Access and exported to Excel would that affect the number of worksheets in Excel? If so, up or down?
How much data? 1 character in one cell per worksheet?
Multiply the max number of columns times the max number of rows times 255 to get a gross number as a size for one worksheet. You might use a number smaller than 255 to get a more realistic estimate since spreadsheets tend to be largely numbers and the numbers are normally 8 bytes or less.
Multiply the max number of columns times the max number of rows times 255 to get a gross number as a size for one worksheet. You might use a number smaller than 255 to get a more realistic estimate since spreadsheets tend to be largely numbers and the numbers are normally 8 bytes or less.
The limit isn't really about the number of worksheets. The limit is the amount of data in the cells.
@Pat
The column limit is greatly expanded in 2007 and beyond
The column limit is greatly expanded in 2007 and beyond
That is why I didn't specify it. The column and row limits depend on what version of Excel you are using and even differ between 32-bit and 64-bit versions. The OP didn't specify which version of Office and he didn't specify if it was 32-bit or 64-bit. The version of Windows isn't relevant to the calculation and that is what he gave us.
I thought your "255" comment referred to the (old) column limit.
ASKER
Sorry, thought I posted Excel version, etc.
W7, 64 bit, Excel 2010.
Workbook has about 22 columns of plain data with approx 9000 rows. The purpose is to break the 9000 rows by market areas (or smaller groups).
Pat - using your suggested calculation - once I get the number for the size of a worksheet (which for the above is 50,4910,000) - what I I supposed to do with it to calculate the number of possible worksheets in the workbook?
Also, the second part of the question was regarding processing the data in Access and transferring to Excel. Would the estimated number of worksheets be the same?
W7, 64 bit, Excel 2010.
Workbook has about 22 columns of plain data with approx 9000 rows. The purpose is to break the 9000 rows by market areas (or smaller groups).
Pat - using your suggested calculation - once I get the number for the size of a worksheet (which for the above is 50,4910,000) - what I I supposed to do with it to calculate the number of possible worksheets in the workbook?
Also, the second part of the question was regarding processing the data in Access and transferring to Excel. Would the estimated number of worksheets be the same?
the number of worksheets = number of market areas.
If you use the advancedfilter feature, you can get the number of unique values (= market areas). That will be the number of worksheets you will need.
If you use the advancedfilter feature, you can get the number of unique values (= market areas). That will be the number of worksheets you will need.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I wouldn't go anywhere near 1.2 Gb of RAM - and that's probably not the figure you would use anyway, because you can use the hard drive to store more of the RAM.
I would put an upper limit of 50 Mb for comfort, and see how many worksheets you can have with the types of data that you intend to have in it. That'll be your answer.