Home 
Search 
Today's Posts 
#1




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 
#2




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 
#3




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 
#4




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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Insert rows based on specific value  Excel Worksheet Functions  
what formula do i put for column m = column k minus column l in e.  Excel Discussion (Misc queries)  
I need a formula that will sum the top 3 number in a column  Excel Worksheet Functions  
how to enter a formula using column() function for a range  Excel Worksheet Functions  
Formula to reference column heading  Excel Worksheet Functions 