mixed cell reference

[Cherian Mathew]

Hi

I'm trying to copy values from the Y axis on to the X
axis. Say, Column A has rows 2,3,4 with values x,y,z.
I need cells B1, C1, and D1 to have x,y,z respectively.

I tried using a mixed reference and copying it over but
it does not seem to work.
In cell B1 I entered =$A2 and copied the formula over to
C1 and D1, expecting to see $A3 and $A4. Excel does not
seem to respect the relative aspect of the row reference
in the formula, but instead copies the same thing to C1
and D1, and I get the 'x' value in all my target cells.

Any ideas? I'm using Excel 2002.

thx
Cherian

 

[Frank Kabel]

Hi
enter the following in B1:
=OFFSET($A$2,COLUMN()-2,0)
and copy to the right

 

[Trevor Shuttleworth]

Cherian

one way:

In cell B1 put: =INDIRECT("A" &COLUMN()) and drag across

Regards

Trevor

 

[Guest]

that's clever, thanks.. I can use it.

-----Original Message-----
Cherian

one way:

In cell B1 put: =INDIRECT("A" &COLUMN()) and drag across

Regards

Trevor





.

 

[Trevor Shuttleworth]

Cherian

You're welcome. Bear in mind it will only work in similar scenarios to the
one you describe, that is row 2 to column 2, row 3 to column 3, and so on.

Regards

Trevor