Hello, I need help creating a formula, here's what I would like it to do.

My spreadsheet is setup with 119 separate worksheets (named "Site 1" - "Site
119") and one other "Master" worksheet that contains data from each of the
other 119 sites/worksheets.

I need to create a formula in the "Master" sheet to sum a block of cells
from each of the 119 Sites. So, easy enough, the formula is:
=SUM('Site 1'!$BV$9:$BV$20)

But the problem is being able to drag this down 119 rows, changing which
worksheet it pulls the data from, without having to go back 119 times (times
many multiple columns) and changing 'Site 1' to 'Site 2' to 'Site 3', etc.

So the first row will be =SUM('Site 1'!$BV$9:$BV$20)
The second row needs to be =SUM('Site 2'!$BV$9:$BV$20)
....
The 119th row needs to be =SUM('Site 119'!$BV$9:$BV$20)

Is this possible? Can someone please help me out?

Thank you very much,
Keith

Keith

Entered in A1 of the Master sheet

=SUM(INDIRECT("'Site " & (ROW()) & "'!$BV$9:$BV$20"))


Gord Dibben MS Excel MVP

On Thu, 26 Jul 2007 15:50:04 -0700, Keith
wrote:

Hello, I need help creating a formula, here's what I would like it to do.

My spreadsheet is setup with 119 separate worksheets (named "Site 1" - "Site
119") and one other "Master" worksheet that contains data from each of the
other 119 sites/worksheets.

I need to create a formula in the "Master" sheet to sum a block of cells
from each of the 119 Sites. So, easy enough, the formula is:
=SUM('Site 1'!$BV$9:$BV$20)

But the problem is being able to drag this down 119 rows, changing which
worksheet it pulls the data from, without having to go back 119 times (times
many multiple columns) and changing 'Site 1' to 'Site 2' to 'Site 3', etc.

So the first row will be =SUM('Site 1'!$BV$9:$BV$20)
The second row needs to be =SUM('Site 2'!$BV$9:$BV$20)
...
The 119th row needs to be =SUM('Site 119'!$BV$9:$BV$20)

Is this possible? Can someone please help me out?

Thank you very much,
Keith

Thank you, that is quick and easy.

"Gord Dibben" wrote:

Keith

Entered in A1 of the Master sheet

=SUM(INDIRECT("'Site " & (ROW()) & "'!$BV$9:$BV$20"))


Gord Dibben MS Excel MVP

On Thu, 26 Jul 2007 15:50:04 -0700, Keith
wrote:

Hello, I need help creating a formula, here's what I would like it to do.

My spreadsheet is setup with 119 separate worksheets (named "Site 1" - "Site
119") and one other "Master" worksheet that contains data from each of the
other 119 sites/worksheets.

I need to create a formula in the "Master" sheet to sum a block of cells
from each of the 119 Sites. So, easy enough, the formula is:
=SUM('Site 1'!$BV$9:$BV$20)

But the problem is being able to drag this down 119 rows, changing which
worksheet it pulls the data from, without having to go back 119 times (times
many multiple columns) and changing 'Site 1' to 'Site 2' to 'Site 3', etc.

So the first row will be =SUM('Site 1'!$BV$9:$BV$20)
The second row needs to be =SUM('Site 2'!$BV$9:$BV$20)
...
The 119th row needs to be =SUM('Site 119'!$BV$9:$BV$20)

Is this possible? Can someone please help me out?

Thank you very much,
Keith

Thanks for the feedback.

Gord

On Thu, 26 Jul 2007 16:34:00 -0700, Keith
wrote:

Thank you, that is quick and easy.

"Gord Dibben" wrote:

Keith

Entered in A1 of the Master sheet

=SUM(INDIRECT("'Site " & (ROW()) & "'!$BV$9:$BV$20"))


Gord Dibben MS Excel MVP

On Thu, 26 Jul 2007 15:50:04 -0700, Keith
wrote:

Hello, I need help creating a formula, here's what I would like it to do.

My spreadsheet is setup with 119 separate worksheets (named "Site 1" - "Site
119") and one other "Master" worksheet that contains data from each of the
other 119 sites/worksheets.

I need to create a formula in the "Master" sheet to sum a block of cells
from each of the 119 Sites. So, easy enough, the formula is:
=SUM('Site 1'!$BV$9:$BV$20)

But the problem is being able to drag this down 119 rows, changing which
worksheet it pulls the data from, without having to go back 119 times (times
many multiple columns) and changing 'Site 1' to 'Site 2' to 'Site 3', etc.

So the first row will be =SUM('Site 1'!$BV$9:$BV$20)
The second row needs to be =SUM('Site 2'!$BV$9:$BV$20)
...
The 119th row needs to be =SUM('Site 119'!$BV$9:$BV$20)

Is this possible? Can someone please help me out?

Thank you very much,
Keith