mirrored array/matrix

  • Thread starter Thread starter hierarchii
  • Start date Start date
H

hierarchii

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 mirrore
half. In other words, since red-coffee's value is "x", in the colum
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 t
describe what I mean.

It's much like a distance chart on a map. One that you can look to se
how far LA is from Las Vegas, then LA to San Fran, just the same as yo
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 cel
return that value
 
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. Selec
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 th
input part, and the lower left corner contains the formulae to the uppe
right corner. Repeat the procedure for cells C4:C7 and so on.

Manges
 
Thank you.

Is there a way to absolute some of the values in the formula so that
can click and drag
 
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.
 
Back
Top