Using the drag down function and formula referring to other worksh

[Guest]

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

 

[Gord Dibben]

Keith

Entered in A1 of the Master sheet

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


Gord Dibben MS Excel MVP

 

[Guest]

Thank you, that is quick and easy.

Keith

Entered in A1 of the Master sheet

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


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Thanks for the feedback.

Gord

Thank you, that is quick and easy.