Function To Add across Multiple Sheets with a Twist

[Sean]

I am looking for a function that can add up cells across multiple
sheets

I could of course do =Sheet1!A1+Sheet2!A1 etc etc. Problem is that I
have 52 sheets each named after a week end date

My values are all in relative positions and I am looking to add these
up in a sheet called "Summary Report". I add a new sheet each week, so
in effect I'm looking for a formula that will add up all A1 values in
each sheet of the workbook expect anything in "Summary Report". Is
this possible?

Thanks

 

[PCLIVE]

Maybe this:
=SUM(Sheet1:Sheet4!A1)

This will total the value A1 on all the sheets from Sheet1 though Sheet4.
Alter the sheet names as necessary.

HTH,
Paul

 

[Guest]

Add two sheets called (for example) "Start" and "End" with "Start" before
your first data sheet and "End" after the last.

In your Summary Sheet:

=SUM(Start:End!A1)

Add new sheets before the "End" w/sheet

HTH

 

[Guest]

check "Refer to the same cell or range on multiple sheets" in help
to make it so that you do not have to change the formulas, add a sheet at
the beginning called start and a sheet at the end called finish (or what ever
you want to call them"
=sum('start:finish'!A1) will sum all the sheets between, including any
shhets you add later between the two start and finish sheets

 

[Guest]

.... missed the quotes ..

=SUM('Start:End'!A1)

Add two sheets called (for example) "Start" and "End" with "Start" before
your first data sheet and "End" after the last.

In your Summary Sheet:

=SUM(Start:End!A1)

Add new sheets before the "End" w/sheet

HTH

 

[Sean]

check "Refer to the same cell or range on multiple sheets" in help
to make it so that you do not have to change the formulas, add a sheet at
the beginning called start and a sheet at the end called finish (or what ever
you want to call them"
=sum('start:finish'!A1) will sum all the sheets between, including any
shhets you add later between the two start and finish sheets








- Show quoted text -

Thanks Guys

 

[Sean]

Thanks Guys- Hide quoted text -

- Show quoted text -

As a slight addition to this, how would I add up the number of sheets
in my workbook, excluding the sheets "Summary Report","From" and "To"?

Thanks

 

[Guest]

have your summary sheet and to and from sheets outside the range of sheets to
be used in the calculation. if your to and from sheets are what I called
start and finish, just dont have anything in the cells you are adding up.

 

[Brian Ballek]

A slight variation on Sean's question, just to make this a lot more
complicated...er...challenging. I need to summarize data across multiple
sheets where the data to be summed won't always be in the same cell
reference. But it should always have the same label 2 cells to the left.
Could this method be enhanced to have the formula find the right data
in all sheets within the Start:End range based on a given text string?

Brian

 

[Harlan Grove]

across multiple sheets where the data to be summed won't always
be in the same cell reference. But it should always have the same
label 2 cells to the left. Could this method be enhanced to have
the formula find the right data in all sheets within the
Start:End range based on a given text string?

....

No.

If you need possibly different cells on different worksheets, you
can't use 3D references. You could use indirect referencing, but you'd
need a list of the names of all the worksheets you need to process. If
you had such a list in a range named WSLST, and if the labels would
always be in col C and the corresponding values always in col E, and
the labels and values of interest always between rows 5 and 100, and
there were no duplicate labels in C5:C100 in any of these worksheets,
you could use

=SUMPRODUCT(SUMIF(INDIRECT("'"&WSLST&"'!C5:C100"),<label_sought>,
INDIRECT("'"&WSLST&"'!E5:E100")))