Remember Me? 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.

E

 Bob Phillips Posts: n/a In A10: =INDIRECT("A"&COLUMN())

--

HTH

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

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

E

 Dave R. Posts: n/a 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).

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

E

 Posts: n/a 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.

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post bob Excel Worksheet Functions 6 February 29th 08 08:11 PM jenniss Excel Discussion (Misc queries) 5 January 6th 05 09:18 PM havocdragon Excel Worksheet Functions 5 December 15th 04 11:03 PM Mike Peter Excel Worksheet Functions 6 December 8th 04 08:11 AM JimDandy Excel Worksheet Functions 1 November 17th 04 05:06 PM

All times are GMT +1. The time now is 01:05 PM. Copyright ©2004-2023 ExcelBanter.