Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. . |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
How to create a formula for a range of cells | Excel Worksheet Functions | |||
Paste a function as a fixed number | Excel Discussion (Misc queries) | |||
How can I dynamically eliminate blank cells in a given range in E. | Excel Worksheet Functions | |||
How do i get an average that ignores blanks in the range of cells. | Excel Worksheet Functions |