how to get excel to select a value

**imatthews2001** · 09-14-2011, 01:33 PM

i have a magic table..... file attached

magic table.jpg

i also have to cells which have data validation on the x axis and y axis.

can excel select a value from the table based upon the data validation selected ??

**tigeravatar** · 09-14-2011, 01:58 PM

imatthews2001,

If you want excel to actually select the cell, that would require VBA. If you just want to know where it is in the table to reference it quickly, you can use conditional formatting

**imatthews2001** · 09-14-2011, 02:06 PM

i dont need excel to select the cell , i just need it to show the contents of the cell in another worksheet .

**ChemistB** · 09-14-2011, 02:39 PM

Sounds like you want INDEX and MATCH

Let's say your x value is across the top (columns) and the value you want is in sheet1!A8, your y value is in A9

On another sheet,

=INDEX(Sheet1!$B$2:$K$5, MATCH(Sheet1!$A$9, Sheet1!$A$2:$A$5,0), MATCH(Sheet1!$A$8, Sheet1!$B$1:$K$1,0))

Does that work for you?