I am trying to figure out how to reference a list on a seperate worksheet. I
have a list of Airport Codes, with the corresponding city next to it.

On a seperate worksheet, I would like to be able to enter the three-letter
airport code, and have the city pop up automatically next to it.

Any advice? Thanks!

You could use the Vlookup function.

Check out Debra Dalgleish's web page on the subject:

http://www.contextures.com/xlFunctions02.html

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Thom" wrote in message
...
I am trying to figure out how to reference a list on a seperate worksheet.
I
have a list of Airport Codes, with the corresponding city next to it.

On a seperate worksheet, I would like to be able to enter the three-letter
airport code, and have the city pop up automatically next to it.

Any advice? Thanks!

You could use an VLOOKUP formula to populate the city based upon the airport
code.

Assuming that the list is in Sheet2 Cells A1 through B11 and the first cell
in Sheet1 having an airport code is cell A1.

=IF(ISBLANK(A1),"",VLOOKUP(A1,Sheet2!$A$1:$B$11,2, FALSE))

If A1 is blank, put nothing, otherwise extract the value in column 2 of the
lookup table that corresponds with airport code in column A. The optional
FALSE argument forces and exact match instead of an approximate one.



--
Kevin Backmann


"Thom" wrote:

I am trying to figure out how to reference a list on a seperate worksheet. I
have a list of Airport Codes, with the corresponding city next to it.

On a seperate worksheet, I would like to be able to enter the three-letter
airport code, and have the city pop up automatically next to it.

Any advice? Thanks!

VLOOKUP formulas on target sheet will pull the data from source sheet to
appropriate cells.

See Debra Dalgleish's site for more on VLOOKUP and Data
Validation lists for entering the choices.

http://www.contextures.on.ca/xlFunctions02.html

http://www.contextures.on.ca/xlDataVal01.html

Note the section on using DV lists from another worksheet by naming the list.


Gord Dibben MS Excel MVP

On Tue, 16 Oct 2007 15:49:03 -0700, Thom wrote:

I am trying to figure out how to reference a list on a seperate worksheet. I
have a list of Airport Codes, with the corresponding city next to it.

On a seperate worksheet, I would like to be able to enter the three-letter
airport code, and have the city pop up automatically next to it.

Any advice? Thanks!

Thank you very much, Kevin B and RagDyer!!! The VLOOKUP formula is EXACTLY
what I need!

You guys are the best!

Thom


"Kevin B" wrote:

You could use an VLOOKUP formula to populate the city based upon the airport
code.

Assuming that the list is in Sheet2 Cells A1 through B11 and the first cell
in Sheet1 having an airport code is cell A1.

=IF(ISBLANK(A1),"",VLOOKUP(A1,Sheet2!$A$1:$B$11,2, FALSE))

If A1 is blank, put nothing, otherwise extract the value in column 2 of the
lookup table that corresponds with airport code in column A. The optional
FALSE argument forces and exact match instead of an approximate one.



--
Kevin Backmann


"Thom" wrote:

I am trying to figure out how to reference a list on a seperate worksheet. I
have a list of Airport Codes, with the corresponding city next to it.

On a seperate worksheet, I would like to be able to enter the three-letter
airport code, and have the city pop up automatically next to it.

Any advice? Thanks!