creating a drop down list for names and addresses

Thanks to all the people who posted in this thread, your info allowed me to create my drop down lists with addresses. However I need to find out how to go one step further.
I'm writing a purchase order entry form for my company. I've used the above formulas to create drop down lists with addresses for each of our stores and vendors, but I need form to automatically select the correct "ship to:" location. Some stores will always have merchandise shipped to the store regardless of the vendor. Other stores will always have the product shipped to a warehouse location, but in a few stores the product will be shipped either to the store or a warehouse depending on the vendor.
I've been able to work out the formulas for the stores that have shipments always to one location by creating a second address table and linking the selected store to it.

Here is basically what I need:

Store A + Vendor A = Store A
Store A + Vendor B = Store A
Store B + Vendor A = Warehouse
Store B + Vendor B = Store B
Store C + Vendor A = Warehouse
Store C + Vendor B = Warehouse


Is there any way to do this?

I don't think I was clear. I'm not looking to display and actual '+' sign, but more like an "if 'a' and 'b' then 'c'" The user will select a value (e.g. store location) from the drop down list in say cell a1 and select another value (e.g. vendor) from another drop down list in cell b2. The form will generate a value (e.g. shipping location) in a 3rd cell.
If what you posted will do that, then I am confused about the formula you posted.

Here's a example of what I need. I hope the upload works

ratcat,

It looks like that's what I need, but I need it to change the ship to addresses as well. What would be the best way to accomplish that?
I have 6 stores (inc. 1 warehouse) and 5 vendors.

ratcat,

Oh yeah! Help is needed.
Using the workbook you posted, I was able to see how you set up the arguments and emulated them as best I could substituting the information I need.

IF(AND(A1="Clearwater - 300",A7="A-1 Superior, Inc.",Addresses!B8),IF(A1="Bradenton - 500",A7="A-1 Superior, Inc.",Addresses!B8),IF(A1="Dale Mabry - 900",A7="A-1 Superior, Inc.",Addresses!B8)(A1="Hillsborough - 100",Addresses!B8,IF(A1="Hudson - 700",Addresses!B5,IF(A1="Clearwater - 300","addresses!b3",""))))

However, Excel says the function is too large and directed me to the formula ar for help. A window popped up and I entered the firsdt argument, but still it is too large.

What is this mrice you are speaking of?

BTW- Addresses!b8 is a cell I added with the warehouse address. I left a row between it and the stores because I do not want it to show on the drop down list.

Originally Posted by ratcat

Corrected the whole formula *I think* just about when cross eye. lol

Try this

Please Login or Register  to view this content.

You must've gotten a little cross-eyed. Iguess it's getting late there. You missed a ( but I found it. I also had to add a couple arguments I forgot, but it all seems to work now!
Yeah! Here's what I ened with:


=IF(AND(A1="Clearwater - 300",A7="A-1 Superior, Inc."),Addresses!B8,IF(AND(A1="Bradenton - 500",A7="A-1 Superior, Inc."),Addresses!B8,IF(AND(A1="Dale Mabry - 900",A7="A-1 Superior, Inc."),Addresses!B8,IF(A1="Hillsborough - 100",Addresses!B8,IF(A1="Hudson - 700",Addresses!B5,IF(A1="Clearwater - 300",Addresses!B3,IF(A1="Bradenton - 500",Addresses!B4,IF(A1="Dale Mabry - 900",Addresses!B6))))))))

Thanks you SO much for all your help!