Summarizing (Excel 2000)

[xl4me]

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.

[Andrew Cronnolly]

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

[xl4me]

Andrew,

Here is the updated file.

Jay

[Andrew Cronnolly]

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