I cannot for the life of me remember how to do this:

I have an array/matrix that looks like this

Red Blue Green Coffee Tea Coke
Red x
Blue y
Green z
Coffee
Tea
Coke

So Red corresponds with coffee, blue with tea and green with coke.

What I'd like is a way for a cell to return the value of it's mirrored
half. In other words, since red-coffee's value is "x", in the column
under red, i'd like there to be an "x" in the cell (row wise that is)
that is coffee-red.

This is a horrible explanation I know. I'm having a hard time trying to
describe what I mean.

It's much like a distance chart on a map. One that you can look to see
how far LA is from Las Vegas, then LA to San Fran, just the same as you
can start at San Fran and trace your finger to see how far it is to LA.

I only want to type in the values once and have the mirrored cell
return that value.


--
hierarchii
------------------------------------------------------------------------
hierarchii's Profile: http://www.excelforum.com/member.php...o&userid=25943
View this thread: http://www.excelforum.com/showthread...hreadid=393211

You will have to manually link the cells. A short-cut is as follows:
Lets say your table is in range A1:G7 inclusing the headers. Select
cells B3:B7, and enter the formula =TRANSPOSE(IF(C2:G2="","",C2:G2)).
Press control shift enter. The upper right corner of your matrix is the
input part, and the lower left corner contains the formulae to the upper
right corner. Repeat the procedure for cells C4:C7 and so on.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=393211

Thank you.

Is there a way to absolute some of the values in the formula so that I
can click and drag?


--
hierarchii
------------------------------------------------------------------------
hierarchii's Profile: http://www.excelforum.com/member.php...o&userid=25943
View this thread: http://www.excelforum.com/showthread...hreadid=393211

I think you need your matrix offset by 1 column i.e. A1 should be blank, Red
should be in A2 and B1 respectively.
Fill in the appropriate "x's" down the diagonal for the matching pairs
Then, in C2 enter
=OFFSET($A$1,COLUMN()-1,ROW()-1)
and copy across through D2:G2
Any value entered in B3:B7 will be mirrored in D2:G2

Copy the cells down, but not into (or past) the cells with "x's" and any
values entered in the lower triangle will be reflected in the top half.

--
Regards

Roger Govier


"hierarchii" wrote
in message ...

I cannot for the life of me remember how to do this:

I have an array/matrix that looks like this

Red Blue Green Coffee Tea Coke
Red x
Blue y
Green z
Coffee
Tea
Coke

So Red corresponds with coffee, blue with tea and green with coke.

What I'd like is a way for a cell to return the value of it's mirrored
half. In other words, since red-coffee's value is "x", in the column
under red, i'd like there to be an "x" in the cell (row wise that is)
that is coffee-red.

This is a horrible explanation I know. I'm having a hard time trying to
describe what I mean.

It's much like a distance chart on a map. One that you can look to see
how far LA is from Las Vegas, then LA to San Fran, just the same as you
can start at San Fran and trace your finger to see how far it is to LA.

I only want to type in the values once and have the mirrored cell
return that value.


--
hierarchii
------------------------------------------------------------------------
hierarchii's Profile:
http://www.excelforum.com/member.php...o&userid=25943
View this thread: http://www.excelforum.com/showthread...hreadid=393211