Autofill cell/sets of cells based upon value selected from a list.

[Brazil]

I am unsure what this would be called or if for that matter it is even
possible in excel. I would like to create a pull down list containing city
names and then based on the city chosen in the list, have an address
automatically fill in another cell/cells located on the same sheet.

I am trying to create a purchase order that can be primarily filled out
using the mouse and can be fairly automated based upon certain standard
selections. Any direction would be appreciated.

 

[Martin Fishlock]

Hi HB:

It is possible to do it excel.

You need to look at data validation and use lists.

You need a table of the cities and I recomend the addresses.

If the table is on another sheet you need to name the table and use the name
not the sheet reference in the data validation.

OK so in say sheet cities

A1=city
B1=address1
C1=address2
.....
A2=Bangkok
B2=10 Silom Road
C2=10110

A3=London
B3=5 Oxford Steet
C3=NW1 4WE

A4=New York
B4=105 Fifth Avenue
C4=12354

You now define a name range A2:A4 and say call it city. (select the range
A2:A4 and then in the name box on the left of the formula bar enter city).

You now go back to your form sheet (in the same workbook) and

in say cell D1 from the menu select data and validation...

from the allow box select list and in the source enter =city

Now when you click on the cell you get the cities.

to get the addresses use vlookup

=VLOOKUP(D1,cities!A:C,2,FALSE)

and this will get the address1

and

=VLOOKUP(D1,cities!A:C,3,FALSE)

will get address2.

You can also use dynamic ranges for the cities have a look at

Contextures: http://www.contextures.com/xlNames01.html#Dynamic

for an example.