Using the drag down function and formula referring to other worksh

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Keith

Entered in A1 of the Master sheet

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


Gord Dibben MS Excel MVP
 
Thank you, that is quick and easy.

Gord Dibben said:
Keith

Entered in A1 of the Master sheet

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


Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top