How to paste INDIRECT function to range of cells?
 

IN OfficeXP/Excell2002:

I have cell D4 on one worksheet with the formula :

=INDIRECT(D$1&"!J4")
[it gets the other worksheet name from thisworksheetcell D1]


and I want to paste it into cells D5-D50, and have J4 increment
accordingly, so that in cell D5 the formula looks like this

=INDIRECT(D$1&"!J5")

etc; J6, J7, etc.
Whenever I paste this kind of formula though, the D1 (worksheetname)
reference increments or not, depending on the $, but the cell reference
doesn't increment, no matter what. Doing all this manually, one cell at a
time, will be a royal pain. Can someone please suggest a solution?

THANKS.

Hi!

Try this:

=INDIRECT(D$1&"!J"&ROW(4:4))

Copy as needed.

Biff

-----Original Message-----
IN OfficeXP/Excell2002:

I have cell D4 on one worksheet with the formula :

=INDIRECT(D$1&"!J4")
[it gets the other worksheet name from
thisworksheetcell D1]


and I want to paste it into cells D5-D50, and have J4
increment
accordingly, so that in cell D5 the formula looks like
this

=INDIRECT(D$1&"!J5")

etc; J6, J7, etc.
Whenever I paste this kind of formula though, the D1
(worksheetname)
reference increments or not, depending on the $, but the
cell reference
doesn't increment, no matter what. Doing all this
manually, one cell at a
time, will be a royal pain. Can someone please suggest a
solution?

THANKS.
.

Biff wrote...
Try this:

=INDIRECT(D$1&"!J"&ROW(4:4))

Copy as needed.
....

Since the reference is to a cell in a different column in the same row,
an alternative would be using R1C1-style references.

=INDIRECT("'"&D$1&"'!RC10",0)

Mike

Entered in D4 and dragged down to D50.

=INDIRECT(D$1&"!J" & ROW())


Gord Dibben Excel MVP

On Thu, 17 Mar 2005 12:10:43 -0800, Mike Williams
wrote:

IN OfficeXP/Excell2002:

I have cell D4 on one worksheet with the formula :

=INDIRECT(D$1&"!J4")
[it gets the other worksheet name from thisworksheetcell D1]


and I want to paste it into cells D5-D50, and have J4 increment
accordingly, so that in cell D5 the formula looks like this

=INDIRECT(D$1&"!J5")

etc; J6, J7, etc.
Whenever I paste this kind of formula though, the D1 (worksheetname)
reference increments or not, depending on the $, but the cell reference
doesn't increment, no matter what. Doing all this manually, one cell at a
time, will be a royal pain. Can someone please suggest a solution?

THANKS.

Gord Dibben <gorddibbATshawDOTca wrote in
:

=INDIRECT(D$1&"!J" & ROW())

Thanks, all. A great set of alternatives. All worked; I must say, Gord's
seems the most elegant.