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


In article ,
Big Dog wrote:

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.

try this idea.
=INDIRECT("g"&COLUMN(A1)+3)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Big Dog" wrote in message
...
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.

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.
--
David Biddulph

"Big Dog" wrote in message
...
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.