Dragging a formula containing external links
 

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!

Dragging a formula containing external links
 

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



Keith W. wrote:

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

Dragging a formula containing external links
 

thats a nice exercise...
how about the "49" columns ?
--
*****
birds of the same feather flock together..



"Dave Peterson" wrote:

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



Keith W. wrote:

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

Dragging a formula containing external links
 

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.

"Dave Peterson" wrote:

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



Keith W. wrote:

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

Dragging a formula containing external links
 

Hi Keith

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

--
Regards

Roger Govier


"Keith W." wrote in message
...
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.

"Dave Peterson" wrote:

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



Keith W. wrote:

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

Dragging a formula containing external links
 

Roger, you're right, that worked!

Thank you Dave and Roger.

"Roger Govier" wrote:

Hi Keith

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

--
Regards

Roger Govier


"Keith W." wrote in message
...
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.

"Dave Peterson" wrote:

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



Keith W. wrote:

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

Dragging a formula containing external links
 

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


driller wrote:

thats a nice exercise...
how about the "49" columns ?
--
*****
birds of the same feather flock together..

"Dave Peterson" wrote:

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



Keith W. wrote:

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


--

Dave Peterson

Dragging a formula containing external links
 

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


Keith W. wrote:

Roger, you're right, that worked!

Thank you Dave and Roger.

"Roger Govier" wrote:

Hi Keith

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

--
Regards

Roger Govier


"Keith W." wrote in message
...
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.

"Dave Peterson" wrote:

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



Keith W. wrote:

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





--

Dave Peterson

Dragging a formula containing external links
 

I thought that you were the original poster.

driller wrote:

thats a nice exercise...
how about the "49" columns ?
--
*****
birds of the same feather flock together..

"Dave Peterson" wrote:

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



Keith W. wrote:

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


--

Dave Peterson