variable references sidewards

[geeky1]

Hi,
I want to drag a fixed column with variable row reference ($A1) sidewards instead of downwards, so cell B1 displays ($A2). How to do this?

Kindest regards

 

[Chihiro]

Few ways to do it.
1. =INDIRECT("$A"&COLUMN(B1))
Copy across

2. Transpose function. See link for detail.
http://www.techonthenet.com/excel/formulas/transpose.php

3. =OFFSET($A1,COLUMN(A1),)
Copy across

Edit: Adjusted reference to start at A2 (all formula in B2)

 

[geeky1]

thanks a lot!