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
dmh01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JBrITCommented:
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.
0
dmh01Author Commented:
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
0
JBrITCommented:
Find mockup attached. Formulas should be easy enough to follow, just ask if you get stuck.
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

ArgentiCommented:
Hello,

Here you'll find the attached file with your requested features. Please notice the new Companies sheet and its structure. You can fill it with your own list of companies.

Good luck!
Stylus-Local-Purchase-Order-Mas2.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ArgentiCommented:
Hi Dave,

So, did you finally find a solution yourself?
0
dmh01Author Commented:
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
0
JBrITCommented:
Hi, apologies.
Seems the business domain doesn't like me uploading! I'll upload it again once home this evening.
0
dmh01Author Commented:
Thanks for your help Argenti, the solution worked well and allowed me to tailor it to suit my needs.
0
ArgentiCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.