Drop Down Variable List help

[miinstrel]

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:

KCK PO doc.xlsx
	A	B	C	D	E	F	G	H	I	J	K
3	SOLD TO:	TEST COMPANY		SHIP TO:	Select Ship To Address
4		test address			#N/A
5		test city, test state, test zip			#N/A
6		test country			#N/A
7		TEL: test phone			#N/A
PO

Cell Formulas
Range	Formula
B4	B4	=VLOOKUP(B$3,'Sell To'!$A:$L,2,FALSE)
B5	B5	=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))
B6	B6	=VLOOKUP(B$3,'Sell To'!$A:$L,6,FALSE)
B7	B7	=CONCATENATE("TEL: ",VLOOKUP(B$3,'Sell To'!$A:$L,7,FALSE))
E3	E3	=IF(ISERROR(VLOOKUP(B3,'Ship To'!A:A,1,FALSE)),B3,"Select Ship To Address")
E4	E4	=IF(ISERROR(VLOOKUP(B$3,'Ship To'!A:A,1,FALSE)),B4,VLOOKUP(E$3,'Ship To'!$B:$N,2,FALSE))
E5	E5	=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)))
E6	E6	=IF(ISERROR(VLOOKUP(B$3,'Ship To'!A:A,1,FALSE)),B6,VLOOKUP(E$3,'Ship To'!$B:$N,6,FALSE))
E7	E7	=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
Cell	Allow	Criteria
B3:C3	List	='Sell To'!$A:$A
E3:K3	Custom	=VLOOKUP(B3,'Ship To'!A:B,2,FALSE)

The "Sell To" tab:

KCK PO doc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	SELL CUSTOMER	SELL TO ADDRESS	SELL CITY	SELL STATE	SELL ZIP	SELL COUNTRY	SELL PHONE	PAY TERMS	SHIP TERMS	FREIGHT	O/I DISCOUNT	PRICE GROUP
2	TEST COMPANY	test address	test city	test state	test zip	test country	test phone
Sell To

The "Ship To" tab:

KCK PO doc.xlsx
	A	B	C	D	E	F	G	H
1	SELL CUST REFERENCE	SHIP CUSTOMER	SHIP TO ADDRESS	SHIP CITY	SHIP STATE	SHIP ZIP	SHIP COUNTRY	SHIP PHONE
2	test company	test co ship to #1	test shipping address #1	city 1	state 1	zip 1	country 1	phone 1
3	test company	test co ship to #2	test shipping address #2	city 2	state 2	zip 2	country 2	phone 2
Ship To

 

[mart37]

=OFFSET('Ship To'!B1,MATCH(B3,'Ship To'!A2:A100,0),,COUNTIF('Ship To'!A2:A100,B3))

 

[miinstrel]

You are such a wonderful human being. Thank you.