Drop down menu and two dimensional lookup in Excel 2003
 

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

Drop down menu and two dimensional lookup in Excel 2003
 

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.)

"THE BIG O" <THE BIG wrote in message
...
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

Drop down menu and two dimensional lookup in Excel 2003
 

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

I will try to be more detailed he

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" wrote:

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.)

"THE BIG O" <THE BIG wrote in message
...
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

Drop down menu and two dimensional lookup in Excel 2003
 

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.

"THE BIG O" wrote in message
...
Bob, thank you for your response. I went ahead and tried that and it did
not
work.

I will try to be more detailed he

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" wrote:

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.)

"THE BIG O" <THE BIG wrote in message
...
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

Drop down menu and two dimensional lookup in Excel 2003
 

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" wrote:

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.

"THE BIG O" wrote in message
...
Bob, thank you for your response. I went ahead and tried that and it did
not
work.

I will try to be more detailed he

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" wrote:

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.)

"THE BIG O" <THE BIG wrote in message
...
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

Drop down menu and two dimensional lookup in Excel 2003
 

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?

"THE BIG O" wrote in message
...
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" wrote:

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.

"THE BIG O" wrote in message
...
Bob, thank you for your response. I went ahead and tried that and it
did
not
work.

I will try to be more detailed he

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" wrote:

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.)

"THE BIG O" <THE BIG wrote in message
...
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

Drop down menu and two dimensional lookup in Excel 2003
 

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.

"THE BIG O" wrote in message
...
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" wrote:

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.

"THE BIG O" wrote in message
...
Bob, thank you for your response. I went ahead and tried that and it
did
not
work.

I will try to be more detailed he

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" wrote:

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.)

"THE BIG O" <THE BIG wrote in message
...
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

Drop down menu and two dimensional lookup in Excel 2003
 

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

Thanks

"Bob Tarburton" wrote:

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.

"THE BIG O" wrote in message
...
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" wrote:

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.

"THE BIG O" wrote in message
...
Bob, thank you for your response. I went ahead and tried that and it
did
not
work.

I will try to be more detailed he

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" wrote:

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.)

"THE BIG O" <THE BIG wrote in message
...
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

Drop down menu and two dimensional lookup in Excel 2003
 

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

"Bob Tarburton" wrote:

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.

"THE BIG O" wrote in message
...
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" wrote:

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.

"THE BIG O" wrote in message
...
Bob, thank you for your response. I went ahead and tried that and it
did
not
work.

I will try to be more detailed he

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" wrote:

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.)

"THE BIG O" <THE BIG wrote in message
...
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