Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    what is best way to get totals (2003 sp2)

    I have a workbook with 4 sheets (TASK 1, TASK 2, TASK 3, TASK 4). The user will enter a number in range(B38:B44) that corresponds with an equipment type. The range is the same on all 4 sheets. What I need is a total of all 4 sheets by equipment type. The one catch is that the type used in cell B38 on TASK 1, might be used in cell B40 in TASK 3, so i need the totals for only these 2 sheets. For planning purpose lets say the total I need is in column 'A' of the corresponding range and sheet. Can some point me in a direction that might not require the most gruesome nested IF?

    For equipment type 21, the total will be stored in 'lbl621Lds'
    23 in 'lbl623Lds'
    25 in 'lbl627Lds'
    56 in 'lblRent613Lds'
    57 in 'lblRent613Lds'
    58 in 'lblRent621Lds'
    59 in 'lblRent623Lds'
    60 in 'lblRent627Lds'
    61 in 'lblRent631Lds'

    on a form.

    thanks,
    jackal

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: what is best way to get totals (2003 sp2)

    I'd use one sheet that contains all the data in a database-like table (with an extra column to identify the task). This makes it much easier to create a summary, for example in the form of a pivot table.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: what is best way to get totals (2003 sp2)

    Here is a picture if the form that i ended up with.
    Attached Images Attached Images

  4. #4
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: what is best way to get totals (2003 sp2)

    The code that goes with the above form is in the attached txt file.
    I ended up going with the "IF" ! ! !

    Seeing as this will be used to gather info from a bunch of workbooks, I did not want to add any more sheets to the workbooks. If someone has time to look at the code and can tell me a shorter way to accomplish what I have written, that would be great.

    thanks,
    jackal
    Attached Files Attached Files

Posting Permissions

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