Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grand totals on another worksheet.
I have a worksheet for user data entry that consists of a few columns of
names and descriptions and monthly columns for quantities. Users enter their data and are free to create subtotals based on the text column entries to evaluate their data. Each workbook also needs the monthly grand totals on other worksheets, regardless of whether the user has subtotals on or not. Is there any way to do this using functions, or do I have to resort to VBA? -- Al C |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Grand totals on another worksheet.
I found an answer to my own question in Exccel 2003, but it should work for
any version. For a simple two column example with headings of NAMES and JAN in row 4, the grand total is: =IF(ISNA(VLOOKUP("GRAND TOTAL",A4:B100,2,FALSE)),SUM(B5:B100),VLOOKUP("GRA ND TOTAL",A4:B100,2,FALSE)) It's important that the VLOOKUP range include the heading row, but the SUM begins with the first data row, even though the VLOOKUP function usually isn't used that way. If the VLOOKUP range begins on the first data row and the user puts subtotals at the top, the range gets reset if subtotals are turned off, but it does not return when subtotals are turned back on. The SUM range also get reset, but it's correct when subtotals are turned off. If the user puts subtotals at the bottom, it doesn't matter. For my case with multiple text columns, the function just has to be nested to cover all the possible column subtotals. -- Al C "Al" wrote: I have a worksheet for user data entry that consists of a few columns of names and descriptions and monthly columns for quantities. Users enter their data and are free to create subtotals based on the text column entries to evaluate their data. Each workbook also needs the monthly grand totals on other worksheets, regardless of whether the user has subtotals on or not. Is there any way to do this using functions, or do I have to resort to VBA? -- Al C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't get Grand Totals (rows) to appear | Excel Discussion (Misc queries) | |||
Need help with grand totals in pivot table | Excel Discussion (Misc queries) | |||
PivotTable Grand Totals By Item | Excel Discussion (Misc queries) | |||
More Grand Totals | Excel Worksheet Functions | |||
Grand Totals @ Same Place | Excel Worksheet Functions |