asked on

Excel 2007 dropdown menu to select a delivery address

Hi,

So I am working on an order form for my company and am trying to work out how I can simply click on a cell and then from the drop down select either NSW, VIC, QLD, etc and then have the address appear in the cells below.

Is there a simple way to do this? I have attached the file I have set up so far, just need a push in the right direction.

Thanks,

Dave
-Stylus-Local-Purchase-Order-Mas.xlsm

JBrIT

Hi,

Looks like you will need a drop-down menu on your main sheet and then a VLOOKUP table on another sheet.

See the links below:

Drop-down List: http://spreadsheets.about.com/od/datamanagementinexcel/qt/20071113_drpdwn.htm

VLOOKUP:
http://spreadsheets.about.com/od/excelfunctions/qt/080722_vlookup.htm

You'll need 1 sheet displaying your drop-down list information. Although it's better to hide it in a column on the main sheet.

You'll then need another sheet with your addresses on and a short vlookup function on each address line effectively saying:

first line: "if customer is X, then pick address line 1 from sheet y", second line "if address line 1 is z, then pick address line 2 from sheet y" etc.

Hope this helps.

P.S

I was trying to edit your attached spreadsheet to give you an example, but my work laptop doesn't like downloaded files. I'll see if I can come up with something for you if the links above don't help.

dmh01

ASKER

Hi JBrIT,

Thanks for he possible solution, I will look at it when back in office tomorrow. In the meantime I just realised that the sheet is locked, here is the unlocked file.

Thanks for your help!
-Stylus-Local-Purchase-Order-Mas.xlsm

JBrIT

Find mockup attached. Formulas should be easy enough to follow, just ask if you get stuck.

ASKER CERTIFIED SOLUTION

Argenti

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Argenti

Hi Dave,

So, did you finally find a solution yourself?

dmh01

ASKER

Hi JBrIT,

Your attachment does not seem to be there, can you please send again. Thanks.

Argenti, I have not had a chance to go through any solutions as yet as I have been busy with other more pressing work. I will take a look at your solution and come back to you soon, thanks for your patience and help!

Regards,

Dave

JBrIT

Hi, apologies.
Seems the business domain doesn't like me uploading! I'll upload it again once home this evening.

dmh01

ASKER

Thanks for your help Argenti, the solution worked well and allowed me to tailor it to suit my needs.

Argenti

You're welcome.