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

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


--

"Sean" wrote in message
ps.com...
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

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" wrote:

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

.... missed the quotes ..

=SUM('Start:End'!A1)


"Toppers" wrote:

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" wrote:

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

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

Toppers wrote:
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" wrote:

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

Brian Ballek wrote...
....
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")))

Brian Ballek wrote...
....
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")))

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

Toppers wrote:
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" wrote:

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

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

"Sean" wrote:

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

On Aug 6, 4:54 pm, bj wrote:
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



"Sean" wrote:
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- Hide quoted text -

- Show quoted text -

Thanks Guys

On Aug 6, 5:11 pm, Sean wrote:
On Aug 6, 4:54 pm, bj wrote:





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

"Sean" wrote:
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- Hide quoted text -

- Show quoted text -

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

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.

"Sean" wrote:

On Aug 6, 5:11 pm, Sean wrote:
On Aug 6, 4:54 pm, bj wrote:





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

"Sean" wrote:
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- Hide quoted text -

- Show quoted text -

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