MrExcel Publishing
Your One Stop for Excel Tips & Solutions

quick trick?


Posted by laura jean on February 07, 2002 10:27 AM

how to create a column of cells A1:A100 that refer to cells across a row B1:DH1???

the best way that I've come up with is creating a row
of cells B2:DH2 refering to B1:DH1, then convert to
absolute references via: *text edit* (RPITB) the
formulas (^H) to replace all "=" with "=$" and all "1"
with "$1"... then copy and Paste Special, Transpose
into A1:A100

(this is tedious when working with more than one row)


Posted by IML on February 07, 2002 10:35 AM

You could also try
=INDIRECT(ADDRESS(1,ROW()+1))
in A1 and copy it down.

I'm not sure if this what you're after as B1:DH1 is more than 100 items, though.

Posted by Mark W. on February 07, 2002 1:53 PM

Furthermore...

If you'd rather have just a cell reference as the
end result consider using...

="="&ADDRESS(1,ROW()+1)

..., Copy/Paste Special Values over these
column A formulas, and then use Edit | Replace...
to replace "=" with "=" to change the text
string into a formula.