picking a matrix cell using drop downs

[stuo81]

Hi I am trying to get excel to pick a cell from a hiden matrix and showing it on my worksheet. I want to pick the cell using 2 drop down boxes as bellow - 1 drop down picks option on X axis and the second picks using the Y axis and then the coresponding cell in the matrix is displayedMatrix examplehttp://i1149.photobucket.com/albums/o596/stuolsen81/matixexample_zpsf7de55c2.jpgDrop downs examplehttp://i1149.photobucket.com/albums/o596/stuolsen81/dropdowns_zps240ee048.jpgI am a novice with litytle experience of excel so please if you are kind enough to respond keep it nice and simple for me.Many thanks

 

[L. Howard]

Here's one way, if I understand correctly.

And for reference:
y1 to y5 is C2:C6
x1 to x5 is D1:H1
Where y2 is a value from drop down 1. (cell A1)
Where x4 is a value from drop down 2. (cell B1)
Where you see the value 2 below y2 the cell formula is =MATCH(A1,C2:C6,0)
Where you see the value 4 below 2 the cell formula is =MATCH(B1,D1:H1,0)
Where you see the value q below 4 the cell formula is =INDEX(D2:H6,A2,A3)

[TABLE="width: 432"]


 <colgroup><col style="width: 54pt;" span="8" width="72">
 <tbody>[TR]

  [TD="width: 72, bgcolor: transparent"]y2[/TD]

  [TD="width: 72, bgcolor: transparent"]x4[/TD]

  [TD="class: xl65, width: 72, bgcolor: transparent"][/TD]

  [TD="class: xl65, width: 72, bgcolor: transparent"]x1[/TD]

  [TD="class: xl65, width: 72, bgcolor: transparent"]x2[/TD]

  [TD="class: xl65, width: 72, bgcolor: transparent"]x3[/TD]

  [TD="class: xl65, width: 72, bgcolor: transparent"]x4[/TD]

  [TD="class: xl65, width: 72, bgcolor: transparent"]x5[/TD]

 [/TR]

 [TR]

  [TD="class: xl66, bgcolor: transparent"]2[/TD]

  [TD="bgcolor: transparent"][/TD]

  [TD="class: xl65, bgcolor: transparent"]y1[/TD]

  [TD="class: xl65, bgcolor: transparent"]a[/TD]

  [TD="class: xl65, bgcolor: transparent"]f[/TD]

  [TD="class: xl65, bgcolor: transparent"]k[/TD]

  [TD="class: xl65, bgcolor: transparent"]p[/TD]

  [TD="class: xl65, bgcolor: transparent"]u[/TD]

 [/TR]

 [TR]

  [TD="class: xl66, bgcolor: transparent"]4[/TD]

  [TD="bgcolor: transparent"][/TD]

  [TD="class: xl65, bgcolor: transparent"]y2[/TD]

  [TD="class: xl65, bgcolor: transparent"]b[/TD]

  [TD="class: xl65, bgcolor: transparent"]g[/TD]

  [TD="class: xl65, bgcolor: transparent"]l[/TD]

  [TD="class: xl65, bgcolor: transparent"]q[/TD]

  [TD="class: xl65, bgcolor: transparent"]v[/TD]

 [/TR]

 [TR]

  [TD="bgcolor: transparent"]q[/TD]

  [TD="bgcolor: transparent"][/TD]

  [TD="class: xl65, bgcolor: transparent"]y3[/TD]

  [TD="class: xl65, bgcolor: transparent"]c[/TD]

  [TD="class: xl65, bgcolor: transparent"]h[/TD]

  [TD="class: xl65, bgcolor: transparent"]m[/TD]

  [TD="class: xl65, bgcolor: transparent"]r[/TD]

  [TD="class: xl65, bgcolor: transparent"]w[/TD]

 [/TR]

 [TR]

  [TD="bgcolor: transparent"][/TD]

  [TD="bgcolor: transparent"][/TD]

  [TD="class: xl65, bgcolor: transparent"]y4[/TD]

  [TD="class: xl65, bgcolor: transparent"]d[/TD]

  [TD="class: xl65, bgcolor: transparent"]i[/TD]

  [TD="class: xl65, bgcolor: transparent"]n[/TD]

  [TD="class: xl65, bgcolor: transparent"]s[/TD]

  [TD="class: xl65, bgcolor: transparent"]x[/TD]

 [/TR]

 [TR]

  [TD="bgcolor: transparent"][/TD]

  [TD="bgcolor: transparent"][/TD]

  [TD="class: xl65, bgcolor: transparent"]y5[/TD]

  [TD="class: xl65, bgcolor: transparent"]e[/TD]

  [TD="class: xl65, bgcolor: transparent"]j[/TD]

  [TD="class: xl65, bgcolor: transparent"]o[/TD]

  [TD="class: xl65, bgcolor: transparent"]t[/TD]

  [TD="class: xl65, bgcolor: transparent"]y[/TD]

 [/TR]


</tbody>[/TABLE]

Regards,
Howard

 

[stuo81]

Thats fantastic Howard. Thanks so much - I can't believe you understood what I wanted from my post ha. I have spent till now (when I've had chance) sorting it to work with my desired info and Matrix etc.

I know have another issue I need to have a 1st drop down box which depending on what is chosen from that list will affect which matrix (one of two) the second 2 drop downs will choose as you have already explained the cell from the relevant matrix.

The reason I need the 1st drop down to hold a long list of items but only choose from 1 of 2 matrices is that as well as choosing the relevant matrix it also needs to display info from another un related hidden list to be displayed in a specific cell




Again any help form yourself or anyone else would be much appreciated

S

 

[L. Howard]

You can send me a workbook example of what you are trying to do and I will take a look at it.

Include concise instructions of what you want to happen.
Where you want it ti happen.
And an example of what the correct results would look like if all the formulas worked as you want them to.

remove the spaces

lh kittle @ com cast . net

Howard

 

[stuo81]

Thanks so much - I'm off work for just over a week now so when I get back in I'll send you a cleaned up mock up with some details thanks again

S