summarizing worksheet data

[Jim]

Hi,

I want to create what should be a relatively simple spreadsheet with, say,
fifty worksheets in the workbook. Each worksheet would be identical in terms
of number of rows & columns, as well as the type of data those columns
contain. On the first worksheet, however, I'd like to be able to summarize
the cumulative totals of all the other sheets. So, for instance, if cell a20
on worksheets 2 through 50 contained a formula that calculated a sum of the
figures in a1:a19, I'd like to put a formula in a cell in the first
worksheet that will total the cell a20 in all the other worksheets.

Is there a simple way to do this?

Thanks in advance,

Jim

 

[Ken Johnson]

Hi,

I want to create what should be a relatively simple spreadsheet with, say,
fifty worksheets in the workbook. Each worksheet would be identical in terms
of number of rows & columns, as well as the type of data those columns
contain. On the first worksheet, however, I'd like to be able to summarize
the cumulative totals of all the other sheets. So, for instance, if cell a20
on worksheets 2 through 50 contained a formula that calculated a sum of the
figures in a1:a19, I'd like to put a formula in a cell in the first
worksheet that will total the cell a20 in all the other worksheets.

Is there a simple way to do this?

Thanks in advance,

Jim

Hi Jim,

See Excel Help "Refer to the same cell or range on multiple sheets"

Ken Johnson

 

[Lori]

=SUM(Sheet2:Sheet50!A20)

 

[Lori]

There is a shortcut for entering this:

=SUM('*'!A20)

where the '*' automatically converts to all sheets other than the
activate one. Excel 2002 seems to have introduced a bug where this can
crash the application. Does anyone else have the same problem?

 

[Dave Peterson]

It worked ok in xl2003 for my simple test.

(I had never seen this before!)

 

[Lori]

I have found it can crash on 2002 if I first omit the single quotes and
add then add them after.

I came across it by accident and haven't seen it documented - think it
probably goes back to XL5 days and used to work fine in 97. It can be
useful for referencing specific sheets in large workbooks e.g. '*abc*'!
evaluates to all sheets containing "abc".

 

[Lori]

I have found it can crash on 2002 if I first omit the single quotes and
add then add them after.

I came across it by accident and haven't seen it documented - think it
probably goes back to XL5 days and used to work fine in 97. It can be
useful for referencing specific sheets in large workbooks e.g. '*abc*'!
evaluates to all sheets containing "abc".

 

[RagDyeR]

You're right Lori.

I crashed my XL02 by being a little lazy.

Copied a working formula to another cell:

=SUM(Sheet2:Sheet5!F1)

Selected <<Sheet2:Sheet5>> in the formula bar,
And typed << '*' >>

As I typed the last single quote, XL crashed!
I *didn't* even have time to hit <Enter> before it happened ! ! !

After recovering the WB, entering the formula from scratch worked perfectly
fine.

When Windows asked for the error report to be sent, I OK'd it.
Got a message back that a fix existed.
Was told to update Office.
I update Windows religiously, can't remember when I last updated Office.

After the Office update, tried the same formula revision again, and XL
*STILL* crashed.
Sending the error report this time generated *no* "able to fix" message.

Haven't tested if revising the '*ABC*' type argument has the same (crash)
results.

Have you?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


I have found it can crash on 2002 if I first omit the single quotes and
add then add them after.

I came across it by accident and haven't seen it documented - think it
probably goes back to XL5 days and used to work fine in 97. It can be
useful for referencing specific sheets in large workbooks e.g. '*abc*'!
evaluates to all sheets containing "abc".

 

[Dave Peterson]

Hey, that one is neat, too.

(Not the crashing part <bg>)

I have found it can crash on 2002 if I first omit the single quotes and
add then add them after.

I came across it by accident and haven't seen it documented - think it
probably goes back to XL5 days and used to work fine in 97. It can be
useful for referencing specific sheets in large workbooks e.g. '*abc*'!
evaluates to all sheets containing "abc".

 

[Lori]

So it can crash in 03 and probably 07 too? To be safe you could
enter the formula as text (with leading apostrophe) and then evaluate
using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
defined name works - though stability is obviously questionable.

 

[Dave Peterson]

It did not crash for me in xl2003. (Sorry about the confusion.)

So it can crash in 03 and probably 07 too? To be safe you could
enter the formula as text (with leading apostrophe) and then evaluate
using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
defined name works - though stability is obviously questionable.

 

[RagDyer]

Works fine in XL2k!

So it's just the one on the middle - XL02.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

So it can crash in 03 and probably 07 too? To be safe you could
enter the formula as text (with leading apostrophe) and then evaluate
using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
defined name works - though stability is obviously questionable.

 

[Lori]

That's good to hear. So there's only one caveat then - be very careful
when using with XL2002!

One further point: aside from stability, evaluating these expressions
without using the formula bar can also add flexibility.

e.g. Suppose you want to sum cells in all sheets beginning with
a,b,c,... You could enter a,b,c,... down column A of the master sheet
and in column B fill down formulas

="=sum('"&A1&"*'a1)"

which could be evaluated by pasting values and then choosing Edit >
Replace "=" with "=".
Or to count all sheets with nonempty cells in a script you could use:

?evaluate("counta('[Book1]*'!a1)")

Anyway we've drifted off Jim's original innocuous question far enough
and I've run out of new material. Glad it lead to a fruitful discussion
though.

Works fine in XL2k!

So it's just the one on the middle - XL02.

 

[Jim]

thanks to all for the input - it was very helpful.

Jim

That's good to hear. So there's only one caveat then - be very careful
when using with XL2002!

One further point: aside from stability, evaluating these expressions
without using the formula bar can also add flexibility.

e.g. Suppose you want to sum cells in all sheets beginning with
a,b,c,... You could enter a,b,c,... down column A of the master sheet
and in column B fill down formulas

="=sum('"&A1&"*'a1)"

which could be evaluated by pasting values and then choosing Edit >
Replace "=" with "=".
Or to count all sheets with nonempty cells in a script you could use:

?evaluate("counta('[Book1]*'!a1)")

Anyway we've drifted off Jim's original innocuous question far enough
and I've run out of new material. Glad it lead to a fruitful discussion
though.

Works fine in XL2k!

So it's just the one on the middle - XL02.
--
Regards,

RD

--------------------------------------------------------------------------

-

Please keep all correspondence within the NewsGroup, so all may benefit

!

--------------------------------------------------------------------------

-