Matrix(?) Help

[EvilMonkey]

I am not sure I'm using the right terminology for this, so am guessing a Matrix.

I need to set up an Excel sheet that will allow me to combine two things and display the result. As a simple example, I have attached a sample spreadsheet with colors, showing the colors in the top row, and the left-column, with the results of being combined in the "body"

What I want to be able to do is just fill out (for example) the "yellow" row, and have the "yellow" column populate automatically, so I don't have to fill out both manually.

Is there a function/formula I can use to achieve this?

In my real-life project, there will be about 300 items I will be combining, so doing it manually would be a huge time effort.

[shg]

Welcome to the forum.

How could Excel know that yellow and red makes orange?

[EvilMonkey]

No, I don't mean that. I mean (looking at my simplified example), if I type in "orange" in C2, then B3 also gets updated to orange (so I don't have to type it there as well).

Expand that out to 300 columns and a corresponding 300 rows, and I would like it to update the "matrix" by only filling out the rows.

Then I would fill out row 2, and column B would populate with the same values. Filling out row 3 would populate column C, row 4 would populate column D, and so on.

To put it another way, I don't want to have to fill out 300 items in Row 2, then type those exact same 300 values going down Column B (would rather it just fill it in automatically since the "combined" values of both are going to be the same)

[shg]

In B3, =INDEX($A$1:$D$4, COLUMNS($A3:B3), ROWS(B$1:B3))

Copy to all cells in the lower left triangle of the matrix, and don't type over them.

[EvilMonkey]

Great, that seems to work....how to I select a "triangle" range though so I can copy/paste this formula all at once?

[shg]

You can't, you need to do it in sections.

[EvilMonkey]

Ok...thank you very much for your help.

[shg]

You're welcome.

Please don't quote whole posts; it's just clutter.