Dragging a formula containing external links

[Guest]

I really hope someone can please help me...

I have a spreadsheet with 119 separate worksheets (Site 1 - Site 119) and
one "master" worksheet. The "master" worksheet is basically the combination
of specific data from each of the 119 sheets, so it has 119 rows (and about
45 columns). In the master, I have a formula that refers to the separate
external worksheets (ie, each row in the "Master" (rows 1-119) will refer to
sheet 1 - sheet 119. To save myself hours of copying the same forumla and
changing the sheet number, I am trying to drag down the formula to keep the
same cell number, but change the referenced sheet number...

SO, the formula in the master in row 1 is:

='Site 1'!$BL$116

I want to drag it down so the external worksheet number changes, but the
cell doesn't, so row 2 will be:

='Site 2'!$BL$116

and row 3 will be:

='Site 3'!$BL$116

all the way to row 119 which will be:

='Site 119'!$BL$116

But when I drag, it just drags 'Site 1'! all the way down.

Can anyone help me? Is this even possible or do I have to type in the
number 119 times (multiplied by about 45 columns I have to do this to).

Thanks!

 

[Dave Peterson]

I'd do this.

I'd put this in A1 (or whatever column).
="$$$='Site "&ROW()&"'!$BL$116"

Then drag down as far as you need.

Then select that range
edit|copy
edit|paste special|values

and finally
with that range still selected:
edit|replace
what: $$$=
with: =
replace all

 

[Guest]

thats a nice exercise...
how about the "49" columns ?

 

[Guest]

I did exactly what you said and it worked great. The only problem is, I have
a bunch of titles and headers for the columns that I need to keep, so I have
to start in row 5 (it is cell Y5 for example). Doing what you explained
started with 'Site 5'!, when it needs to be 'Site 1'!. How do I make row 5
start with 'Site 1'!??? Do I put in a minus 4 (- 4) in that formula you gave
me somewhere to get it to start with 1 instead of 5?

Thanks for all the help to this point.

 

[Roger Govier]

Hi Keith

Instead of ROW(), use ROW(A1) in Dave's formula, then copy down.

 

[Guest]

Roger, you're right, that worked!

Thank you Dave and Roger.

 

[Dave Peterson]

You didn't give enough information for any guess at what those other formulas
would look like -- so I didn't guess.

 

[Dave Peterson]

You could have used this, too:
row()-4

Roger, you're right, that worked!

Thank you Dave and Roger.

 

[Dave Peterson]

I thought that you were the original poster.

thats a nice exercise...
how about the "49" columns ?