Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    St. John's, NFLD

    Lightbulb Unanswered: Efficient 4 tables instead of a sloppy 116


    I'm not totally sure how to explain this, but here goes:

    My spreadsheet carries out various calculations automatically from user input.

    I have a listing of 29 species, I also have to enter multiple values for each life stage per species of which there is 4 life stages.

    So theoretically I would have to create (29 species X 4 life stages) 116 tables.

    What I would like to be able to do is just create a basic 4 table life stage sheet from which I could execute the calculations for one species at a time and have them saved in a final calculations sheet, clear out the life stage tables and start entering values for another species.

    So basically after each calculation on the final calculation sheet, I would want the values "bumped down" each time and not delete the last set of calculated values.

    Is this possible, how complex would it be?

  2. #2
    Join Date
    Oct 2003
    Welcome to the board.

    Sounds like maybe you could just add a couple of columns (for species, and life stage) and thus have only one base sheet. Then all calcuations could be based on one sheet and put on a summary sheet.

    For data entry use the Data Form in Excel, or for more control, use the Enhanced Data Form from John Walkenbach's site.
    old, slow, and confused
    but at least I'm inconsistent!

    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    St. John's, NFLD


    One thing that I failed to mention is that the user will have to choose what species they want, more than likely not all 29 species will be chosen at one time during a session.

    So really I need something dynamic, whereby as stated before I can use the 4 tables over and over again by creating a Macro to clear out the cells, but at the same time saving each "calculation transaction" for each species in a final summary table.

    I hope this further elaborates my situation a little bit.

    Basically I could copy & paste the 4 tables 29 times and have the tables potentially set up to send their data to my summary table, but there has to be a more efficient way??

    Thanks for your help!

    P.S. I have the 4 tables set up the way I do based on a document and the end users would like to keep the format so I can't change that.

Posting Permissions

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