Grand totals on another worksheet.

[Al]

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]

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("GRAND
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.