+ Reply to Thread
Results 1 to 9 of 9

# of worksheets limited by memory

  1. #1
    Nicole Seibert
    Guest

    # 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. #2
    Jim Thomlinson
    Guest

    RE: # 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. #3
    Nicole Seibert
    Guest

    RE: # 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. #4
    Jim Thomlinson
    Guest

    RE: # 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. #5
    ducky
    Guest

    Re: # 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. #6
    Nicole Seibert
    Guest

    RE: # 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. #7
    Tom Ogilvy
    Guest

    RE: # 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. #8
    Jim Thomlinson
    Guest

    RE: # 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. #9
    NickHK
    Guest

    Re: # 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" <NicoleSeibert@discussions.microsoft.com> glsD:3F06ADF3-25CB-44F6-8B22-879C5B6ABBE5@microsoft.com...
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1