How to paste INDIRECT function to range of cells?

M

Mike Williams

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.
 
H

Harlan Grove

Biff wrote...
Try this:

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

Copy as needed.
Click to expand...
....

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)
 
G

Gord Dibben

Mike

Entered in D4 and dragged down to D50.

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


Gord Dibben Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copying a formula containing the INDIRECT function 7
Sumproduct with multiple criteria using indirect formula 4
INDIRECT function 2
INDIRECT function to reference values in another worksheet 1
DSUM criteria 3
How do I automatically increment worksheet number when pasting a . 1
using indirect to return a named range? 1
Link to text and return text into a formula? 5

Top