Excel - mirror formula? 1

[KatherineCoombs]

OK, this might be a little hard to explain, but basically I'm after a simple formula, but it seems impossible to do.

I have a spreadsheet where I want the cell A2 to equal the cell B1, A3 to equal C1, B3 to equal C2 etc...basically I want to create a mirror image along the diagonal starting from A1.

Ordinarily, I could just create a formula and then drag it along, but I can't see a way to do this. Any ideas??

 

[combo]

You can do it in three steps:
1. in the row below, in A2 (or any other) write formula =A$1 and copy right,
2. select formulas in row 2, copy, and paste special witch checked "transpose",
3. clear row 2.

combo

 

[xlbo]

In A2, enter:
=OFFSET($A$1,COLUMN()-1,ROW()-1)
and copy down (and across)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[KatherineCoombs]

Excellent - thanks Geoff!