Hello all,

I'm working on a web-based form. 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))))))))

Set up a lookup table which has each state and its Capital, say on
sheet3 A1:B52. Then you could use a vlookup e.g.

=vlookup(sheet2!d1,sheet3!a1:b52,2,0)

Hope this helps
Rowan

Brablo wrote:
Hello all,

I'm working on a web-based form. 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))))))))

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?


Rowan wrote:
Set up a lookup table which has each state and its Capital, say on
sheet3 A1:B52. Then you could use a vlookup e.g.

=vlookup(sheet2!d1,sheet3!a1:b52,2,0)

Hope this helps
Rowan

Brablo wrote:
Hello all,

I'm working on a web-based form. 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))))))))

Lets say your dropdown is in cell D55, and as stated your list of cities
is in Column A and the associated state is in Column B.
In E55 enter the formula:

=VLOOKUP(D55,$A:$B,2,0)

This will return a #NA error if D55 is blank or contains a city which is
not in the list in column A. You can get around this by adapting the
formula as follows:

=IF(ISNA(VLOOKUP(D55,$A:$B,2,0)),"",VLOOKUP(D55,$A :$B,2,0))

You can find more info on vlookups in excel help and at
http://www.contextures.com/xlFunctions02.html

Regards
Rowan

Brablo wrote:
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?


Rowan wrote:

Set up a lookup table which has each state and its Capital, say on
sheet3 A1:B52. Then you could use a vlookup e.g.

=vlookup(sheet2!d1,sheet3!a1:b52,2,0)

Hope this helps
Rowan

Brablo wrote:

Hello all,

I'm working on a web-based form. 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))))))))