Drop Down Variable List help

miinstrel

New Member
Joined
Aug 26, 2010
Messages
21
Hi everyone!

Putting together a purchase order spreadsheet. Some customers have multiple Ship To addresses.
I want the Ship To section of the main tab to:
1. autofill with the "Sold To" data if the "Sold To" customer name does not exist in Column A of the "Ship To" tab.
2. If the customer name does exist on the "Ship To" tab, then...
2a. Create a drop down list with only the ship to addresses related to that specific customer without having to create a new list/table for each new customer I add to the sheet.

Appreciate any help you're able to offer - stuck on how to make this happen. TYIA!

Main PO doc tab:
Cell Formulas
RangeFormula
B4B4=VLOOKUP(B$3,'Sell To'!$A:$L,2,FALSE)
B5B5=IF(VLOOKUP(B$3,'Sell To'!$A:$L,3,FALSE)="","",VLOOKUP(B$3,'Sell To'!$A:$L,3,FALSE))&IF(VLOOKUP(B$3,'Sell To'!$A:$L,4,FALSE)="","",", "&VLOOKUP(B$3,'Sell To'!$A:$L,4,FALSE))&IF(VLOOKUP(B$3,'Sell To'!$A:$L,5,FALSE)="","",", "&VLOOKUP(B$3,'Sell To'!$A:$L,5,FALSE))
B6B6=VLOOKUP(B$3,'Sell To'!$A:$L,6,FALSE)
B7B7=CONCATENATE("TEL: ",VLOOKUP(B$3,'Sell To'!$A:$L,7,FALSE))
E3E3=IF(ISERROR(VLOOKUP(B3,'Ship To'!A:A,1,FALSE)),B3,"Select Ship To Address")
E4E4=IF(ISERROR(VLOOKUP(B$3,'Ship To'!A:A,1,FALSE)),B4,VLOOKUP(E$3,'Ship To'!$B:$N,2,FALSE))
E5E5=IF(ISERROR(VLOOKUP(B$3,'Ship To'!A:A,1,FALSE)),B5,IF(VLOOKUP(E$3,'Ship To'!$B:$N,3,FALSE)="","",VLOOKUP(E$3,'Ship To'!$B:$N,3,FALSE))&IF(VLOOKUP(E$3,'Ship To'!$B:$N,4,FALSE)="","",", "&VLOOKUP(E$3,'Ship To'!$B:$N,4,FALSE))&IF(VLOOKUP(E$3,'Ship To'!$B:$N,5,FALSE)="","",", "&VLOOKUP(E$3,'Ship To'!$B:$N,5,FALSE)))
E6E6=IF(ISERROR(VLOOKUP(B$3,'Ship To'!A:A,1,FALSE)),B6,VLOOKUP(E$3,'Ship To'!$B:$N,6,FALSE))
E7E7=IF(ISERROR(VLOOKUP(B$3,'Ship To'!A:A,1,FALSE)),B7,"TEL: "&VLOOKUP(E$3,'Ship To'!$B:$N,7,FALSE))
Cells with Data Validation
CellAllowCriteria
B3:C3List='Sell To'!$A:$A
E3:K3Custom=VLOOKUP(B3,'Ship To'!A:B,2,FALSE)


The "Sell To" tab:
KCK PO doc.xlsx
ABCDEFGHIJKL
1SELL CUSTOMERSELL TO ADDRESSSELL CITYSELL STATESELL ZIPSELL COUNTRYSELL PHONEPAY TERMSSHIP TERMS FREIGHT O/I DISCOUNTPRICE GROUP
2TEST COMPANYtest addresstest citytest statetest ziptest countrytest phone
Sell To


The "Ship To" tab:
KCK PO doc.xlsx
ABCDEFGH
1SELL CUST REFERENCESHIP CUSTOMERSHIP TO ADDRESSSHIP CITYSHIP STATESHIP ZIPSHIP COUNTRYSHIP PHONE
2test companytest co ship to #1test shipping address #1city 1state 1zip 1country 1phone 1
3test companytest co ship to #2test shipping address #2city 2state 2zip 2country 2phone 2
Ship To
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
=OFFSET('Ship To'!B1,MATCH(B3,'Ship To'!A2:A100,0),,COUNTIF('Ship To'!A2:A100,B3))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,931
Messages
5,639,061
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top