Cell Reference Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have data in an Excel spreadsheet that's sorted vertically. I need to
reference this vertical data (i.e. G4, G5, G6, etc) to another part of the
spreadsheet so that it shows up horzontally.

I know I can use copy/transpose to do this, but what I'm trying to do is use
the horizontal cell reference (i.e. =G4, G5, etc). I'm trying to avoid
having to type in each cell reference. Is there a way to essentially "lock"
the reference to "G" but have it pull from the next cell down? i.e. G4, G5,
etc.

Hope this makes sense.
 
One way:

Assume you want to put G4:Gx in AA3:yy3:

AA3: =INDEX($G:$G,COLUMN()-23)

Copy across. Adjust the 23 to suit, depending on your starting column
(and row).
 
If, for example, the reference of the first cell of your horizontal range is
H3, then use =OFFSET($G$4,COLUMN()-COLUMN($H$3),0)
Copy across as required.
 
Back
Top