Drop down menu and two dimensional lookup in Excel 2003

[Guest]

I have created a table with 26 columns and 12 rows. I have created two drop
down menus on another tab, one for choosing a header of the columns and the
other for the rows. I want the user to be able to choose one option from
each drop down menu and receive an answer (the intersection of the column and
row for the options chosen) from the table.

I looked at Index/Match, two dimensional lookup, and trying to put vlookup
and Hlookup together. The two dimensional lookup would work but I dont want
the user to have to type in = columnheader rowheader.

Any suggestions?

Thanks

Michael

 

[Bob Tarburton]

Sound more like a straight up index ?
If your table is in Sheet1!A1:Z13 (excluding labels), with drop down menues
linked to Sheet2!A1 for the column headers and Sheet2!A2 for the row labels,
then
=index(Sheet1!A1:Z13,A2,A1)
(on Sheet2) ought to work.
(Will work if your drop down menues are forms. If you are using code then a
more detailed description might be required.)

 

[Guest]

Bob, thank you for your response. I went ahead and tried that and it did not
work.

I will try to be more detailed here:

The Drop down menues are located on Tab1, and the table is located on tab2.
In the table, Column A and Row 1 contains the words that are to be in the
dropdown menu. I created the first list menu on Tab 1 after I highlighted
all of the names in Column Aand gave them a Name. Starting on Row 1 Column
B are my code words and I highlighted all of them and assigned a Name for
them for the other list menu.

I want the user to select the code word and the Name to get the result. My
first thought was to just do a Vlookup or Hlookup but then I would get
results showing either 12 rows or 26 columns, which would be a busy screen.
I don't want the user to get confused because they see more than one answer.

Hope that helps.

Thanks

Michael

 

[Bob Tarburton]

I can't seem to get the named range to work in the drop down list. The index
formula will work if you get the lists to give the proper return. I suggest
copying the column header and Paste speacial/transpose somewhere, then link
your column selection drop down box to the new list.

 

[Guest]

Bob,

I did the copy/paste special value/transpose for both of the lists. Tried
the index again and no luck, getting a #value error.

I have been searching around and my understanding is that one can create a
drop down list via naming the range and then validation. The other option is
to right click on create list. Are you saying the index will not work with
named ranges? Per your comment, the index formula will work if there is a
proper return--I am lost here.

Michael

 

[Bob Tarburton]

Are your drop down lists linked to a specfic cell and is that cell taking on
the proper value?
For example if you select the second row does that put a 2 in a particular
cell?
If you select the third column does that put a 3 in a particular cell?

 

[Bob Tarburton]

The method I am adressing assumes that you selected drop down boxes from the
"forms" toolbar, not from the "control toolbox"
If you are woking with "controls" the problem is probably in your code.
In that case you should ask for help in the excel.programming newsgroup and
post your code with your question.

 

[Guest]

Ok, I see what you are saying now. I will try that.

Thanks

 

[Guest]

I got it to work. Thank you very much Bob.

The method I am adressing assumes that you selected drop down boxes from the
"forms" toolbar, not from the "control toolbox"
If you are woking with "controls" the problem is probably in your code.
In that case you should ask for help in the excel.programming newsgroup and
post your code with your question.