column to row in formula
 

I am trying to reference the data in a column but use it in a row. ie
I have data in a column 'A' as follows:
w
x
y
z

I want to write a formula in cell A10 as follows:
=$A1 (result = w)
When I copy the formula to cell B10 I want the result to = x (ie $A2)

I know how to copy a complete column and then transpose it but I was
wondering if I could accomplish it in a formula.

Thanks in advance
E

In A10: =INDIRECT("A"&COLUMN())

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Evad" wrote in message
...
I am trying to reference the data in a column but use it in a row. ie
I have data in a column 'A' as follows:
w
x
y
z

I want to write a formula in cell A10 as follows:
=$A1 (result = w)
When I copy the formula to cell B10 I want the result to = x (ie $A2)

I know how to copy a complete column and then transpose it but I was
wondering if I could accomplish it in a formula.

Thanks in advance
E

Here's another approach

=OFFSET($A$1,COLUMN()-COLUMN($B:$B),0)

where $B is the column where you first enter the formula (and will return
the value in A1).



"Evad" wrote in message
...
I am trying to reference the data in a column but use it in a row. ie
I have data in a column 'A' as follows:
w
x
y
z

I want to write a formula in cell A10 as follows:
=$A1 (result = w)
When I copy the formula to cell B10 I want the result to = x (ie $A2)

I know how to copy a complete column and then transpose it but I was
wondering if I could accomplish it in a formula.

Thanks in advance
E

Bob Phillips wrote...
In A10: =INDIRECT("A"&COLUMN())
....

INDIRECT is unambiguously volatile. INDEX doesn't seem to be. If so,
then

=INDEX($A:$A,COLUMN())

would be more efficient since there would presumably be many such
formulas.