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?
uadAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You will have problems with the quantity of data. I don't know if you have ever worked with a workbook which is 100 Mb big, but getting one of those even to open, let alone to use, is difficult.
 
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.
0
PatHartmanCommented:
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.
0
aikimarkCommented:
The limit isn't really about the number of worksheets.  The limit is the amount of data in the cells.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
@Pat

The column limit is greatly expanded in 2007 and beyond
0
PatHartmanCommented:
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.
0
aikimarkCommented:
I thought your "255" comment referred to the (old) column limit.
0
uadAuthor Commented:
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?
0
aikimarkCommented:
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.
0
PatHartmanCommented:
what I I supposed to do with it to calculate the number of possible worksheets in the workbook
Divide by the available RAM.

If you are looking at 22 columns and 9,000 rows, that is "nothing" in the present world of spreadsheets.  Why are you worried about having them all in memory at one time? and why do you want them all in the same workbook?  Your own users should probably be using Access reports rather than spreadsheets, especially if you are going to be creating a humongous workbook with many sheets.  You will need to look at the Excel specs to see what the maximum number of sheets in a workbook is.

Access databases are limited to 2G.  It doesn't matter how many rows but tables are limited to 255 columns. Objects have limits also but I don't have the specs handy.  You can find them by searching for Access specifications.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.