Expanded Drop Down Box Question

[Rob]

I am trying to create a drop down box that will automatical populate other
boxes.
I have a worksheet with a list of Locations with their latidue and longitude
coordinates. I am trying to make a drop down box in another worksheet where
I select the location and then excel automatically fills in the next 2 colums
with the lat/long data. Is there a way I can make this happen?

 

[L. Howard Kittle]

Hi Rob,

I believe using VLOOKUP can do what you want.

With the location name in column A and the latitude in column B and the
longitude in column C and the drop down cell is F1...

In G1 =VLOOKUP(F1,A1:C100,2,0) for the Lat
In H1 =VLOOKUP(F1,A1:C100,3,0) for the Long

HTH
Regards,
Howard

 

[Jim Thomlinson]

This requires 3 things. Data validation lists, named ranges and lookup
functions.

A data validation list will allow you to create your drop down. The issue
you will run into is that your list is on a seperate page from the drop down.
To create the named range highlight your list of locations. Just above cell
A1 is the Name box which normally just shows you the address of the active
cell. Select the address in the name box and type in Locations. You have now
created a named range called locations. On the sheet where you want the drop
down select the cell where you want the drop down and Click Data | Validation
| List and in the reference type =Locations. Hit Ok. This cell should now be
a drop down with your list of lcations in it.

Now if you lookup VLookup in XL help you should get enough info on how to
create a lookup function that will reference your location and return the Lat
/ Lon...