Populating a field using a pull-down/combo box menu

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brablo - 13 Sep 2005 21:25 GMT

Hello all,

I'm working on MS Excel. I am trying to find the source code for
something that will allow me to do the following novel procedure with a
pull-down menu and a text box: There is a pull-down menu, and another
text box right besides the pull-down menu. The pull-down menu has the
names of all the states in the USA in it. When one state is selected,
the text box is automatically populated with the capital of that state.

In MS Excel, I've created a novel way to handle this issue using
Boolean logic. Unfortunately, I can only have 7 states in my pull-down
menu, and the source code is very unwieldy:

IF(Sheet2!D1=1,Sheet2!A1, IF(Sheet2!$D$1=2,Sheet2!A2,
IF(Sheet2!$D$1=3,Sheet2!A3, IF(Sheet2!$D$1=4,Sheet2!A4,
IF(Sheet2!$D$1=5,Sheet2!A5, IF(Sheet2!$D$1=6,Sheet2!A6,
IF(Sheet2!$D$1=7,Sheet2!A7, IF(Sheet2!$D$1=8,Sheet2!A8))))))))

Reply to this Message

Barb Reinhardt - 13 Sep 2005 23:33 GMT

Look at the VLOOKUP function and see if that gets you what you want.

> Hello all,
>
[quoted text clipped - 13 lines]
> IF(Sheet2!$D$1=5,Sheet2!A5, IF(Sheet2!$D$1=6,Sheet2!A6,
> IF(Sheet2!$D$1=7,Sheet2!A7, IF(Sheet2!$D$1=8,Sheet2!A8))))))))

Reply to this Message

Brablo - 14 Sep 2005 15:04 GMT

Thanks for your guidance. I think I need some guidance with this
function.

Suppose that I have a column of major cities in the USA in column A.
Column B tells me which state that major city is located in. Some of
the data is:

MajCity    State
San Jose    CA
San Francisco    CA
Detroit    MI
Boston    MA
Albany    NY
New York    NY

I have a pulldown menu that has a list of all the "MajCity". When the
user selects one of the cities, Albany for example, the output is "NY",
and this is to be displayed in E55.

What's the algorithm for this problem?

Reply to this Message

L. Howard Kittle - 14 Sep 2005 16:54 GMT

Hi Brablo,

Per Barb's suggestion of VLOOKUP:

=VLOOKUP(C1,J1:K6,2,0)

Where your dropdown list of cities is in C1
Where your table of cities and states are in J1:K6
The Vlookup formula is in F1

Select a city in C1 and the state is returned in F1.

HTH
Regards,
Howard

> Hello all,
>
[quoted text clipped - 13 lines]
> IF(Sheet2!$D$1=5,Sheet2!A5, IF(Sheet2!$D$1=6,Sheet2!A6,
> IF(Sheet2!$D$1=7,Sheet2!A7, IF(Sheet2!$D$1=8,Sheet2!A8))))))))

Reply to this Message