Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    Ontario, Canada
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summarizing (Excel 2000)

    I have a work book which has 2 data entry sheets for each user (more to be added) and one summar y sheet.

    On the summary sheet I want the totals from each user to be entered automatically with a formula. The user sheets will expand as more data is entered. How can I have the running totals show on the summay sheet?

    Also, how can I have the summary show by monthly totals for each user, since I will have to chart this.

    File is attached.
    Help is much appreciated.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summarizing (Excel 2000)

    Toget the totals onto your summary sheet is simple enough if columns E to N contail only th evalue and totals you are interested in. Assumint th etotal is always corrcetly calculated, and as all your number sare postive you can make use of the MAX function. In B3 of your summary sheet <pre> =MAX(User1!E:E) </pre>

    should give the total, as the total is the Maximum value in the column. You would need to change User1 in the above to User2 for the next user. If you have many users the following formula (in B3) should pick the correct sheet assuming that the sheet name is the same as the user names in coulmn A.<pre> =MAX(INDIRECT(($A3&"!"&(CHAR(67+COLUMN())&":"&CHAR (67+COLUMN())))))</pre>

    That formula can be copied down for each user and across for each column.

    If you insert an additionall column with the formula = Month(A4) etc, on each user sheet, you should then be able to get a monthly summary using that column by either SubTotals or a Pivot Table.

    Andrew C

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    Ontario, Canada
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing (Excel 2000)

    Andrew,

    Here is the updated file.

    Jay
    Attached Files Attached Files

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Summarizing (Excel 2000)

    Jay,

    Attached find your file with a summary for each user on that users sheet.

    When you enter a new record for each user, if you goto row 28 and insert a row before doing so, the formulas will update automatically to include the new entry.

    Andrew C
    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
  •