Trying to Create a drop down list based on 2 columns matching

[TBud1981]

I'm not sure if it's even possible, but I'd like to have a drop down list in column Q for the matching Part #'s in Column R & BA.

the quantities in the columns now are the original pallet quantities, but I'd like a drop down list of the multiples of those quantities if we ship more than just one pallet (multiples are in AP & AS. the multiplication table for the different pallet quantities is from AI1:AY37)

so for example, if R is part # HUB284T-9[ER03] I want a drop down of the multiples of that pallet qty (shown in column AP), but if that part is not shipping and the part # shipping is HUB867T-4#R[ER03], I want a drop down of those multiples (shown in column AS).

Ship Sheets.xlsm
	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG									AP			AS								BA
1		ASN#		Dak#	287969																							162			224								NTN Part #
2	Order	Line	QTY	NTN Part #	CPN	Release	Lot	Pick	LS	NS	RD	PSD	SP	Ship To	Price	PO	Contract #	Plts										324			448								HUB529T-4[EU03]
3																												486			672								HUB399-1#P[EU03]
4																												648			896								HUR093-51[ER03]
5																												810			1120								HUR093-52[ER03]
6		ASN#		Dak#																								972			1344								HUR093-53[ER03]
7	Order	Line	QTY	NTN Part #	CPN	Release	Lot	Pick	LS	NS	RD	PSD	SP	Ship To	Price	PO	Contract #	Plts										1134			1568								HUR102-14[ER03]
8																												1296			1792								HUR122-14[ER03]
9																												1458			2016								HUR122-4[ER03]
10																												1620			2240								HUB867T-4#E[EU03]
11		ASN#																										1782			2464								HUB868T-4#E[EU03]
12	Order	Line	QTY	NTN Part #	CPN	Release	Lot	Pick	LS	NS	RD	PSD	SP	Ship To	Price	PO	Contract #	Plts										1944			2688								HUB335T-8#P[EU03]
13																												2106			2912								HUB284T-9[ER03]
14																												2268			3136								HUB867T-4#R[ER03]
15																												2430			3360								HUB867T-4[ER03]
16																												2592			3584								HUB868T-4#R[ER03]
17		ASN#																										2754			3808								HUB868T-4[ER03]
18	Order	Line	QTY	NTN Part #	CPN	Release	Lot	Pick	LS	NS	RD	PSD	SP	Ship To	Price	PO	Contract #	Plts										2916			4032								TKBA0993[ER03]
19																												3078			4256								TKBA0994[ER03]
20																												3240			4480								HUB877T-2#R[ER03]
21																												3402			4704								HUB878T-2#R[ER03]
22																												3564			4928								HUB878T-2[ER03]
23		ASN#																										3726			5152
24	Order	Line	QTY	NTN Part #	CPN	Release	Lot	Pick	LS	NS	RD	PSD	SP	Ship To	Price	PO	Contract #	Plts										3888			5376
25																												4050			5600
26																												4212			5824
27																												4374			6048
28																												4536			6272
29		ASN#																										4698			6496
30	Order	Line	QTY	NTN Part #	CPN	Release	Lot	Pick	LS	NS	RD	PSD	SP	Ship To	Price	PO	Contract #	Plts										4860			6720
31																												5022			6944
32																												5184			7168
33		ASN#																										5346			7392
34	Order	Line	QTY	NTN Part #	CPN	Release	Lot	Pick	LS	NS	RD	PSD	SP	Ship To	Price	PO	Contract #	Plts										5508			7616
35	6317666	311	162	HUB284T-9[ER03]	52124767AE	#	A21001359780	6508211	#	#	#######	10/25/2023		#	#	#		1										5670			7840
36	6305224	541	224	HUB867T-4#R[ER03]	68349631AA	#	A21001358726	6508212	#	#	#######	10/25/2023		#	#	#		1										5832			8064
37	6305225	541	224	HUB868T-4#R[ER03]	68346852AA	#	A21001357470	6508213	#	#	#######	10/25/2023		#	#	#		1										5994			8288
38	6310466	426	252	TKBA0993[ER03]	04779612AE	#	A21001358626	6508214	#	#	#######	10/25/2023		#	#	#		1
39
Cheryl & Reno

Cell Formulas
Range	Formula
S1	S1	=IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$C$33,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$C$34>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$C$34, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$G$33,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$G$34>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$G$34, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$K$33,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$K$34>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$K$34, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$O$33,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$O$34>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$O$34, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$S$33,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$S$34>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$S$34,"")))))
AF3:AF5,AF35:AF39,AF31:AF32,AF25:AF28,AF19:AF22,AF13:AF16,AF8:AF10	AF3	=IFERROR(SUM(Q3/INDEX(BC:BC,MATCH(R3,BA:BA,0))),"")
S6	S6	=IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$C$37,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$C$38>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$C$38, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$G$37,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$G$38>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$G$38, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$K$37,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$K$38>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$K$38, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$O$37,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$O$38>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$O$38, IF(AND($D$1='https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$S$37,'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$S$38>0),'https://d.docs.live.net/237f5792e79d6491/Work/Excel Spreadsheets/Requirements/[Weekly Requirements.xlsm]Weekly Requirements'!$S$38,"")))))

Cells with Data Validation
Cell	Allow	Criteria
Q13:Q16	List	=$AK$1:$AK$37
Q19:Q22	List	=$AK$1:$AK$37
Q3:Q5	List	=$AS$1:$AS$37
Q31	List	=$AM$1:$AM$37

 

[sijpie]

let me rephrase what you want to achieve.
when the user enters a part number in cell Rx, then the dropdown in cell Qx has to show the multiples of a pallet quantity.

There are different pallet configurations (listed in columns AI:AY, so part numbers are related to these columns.


What I do not understand is how the partnumber is related to a column. You mention # HUB284T-9[ER03] is related to column AP and HUB867T-4#R[ER03] is related to column AS.

Where is this link? How can a macro decide which column to load in the dropdown list in cell Qx?

 

[TBud1981]

all the part numbers from column R are related to the same column (BA).

so if a user enters a part number in R...and it matches BA, I want that specific part number's multiples to only show as a drop down.

ie: user enters HUB284T-9[ER03] in column R, and it matches the part number in BA, I want a drop down of only column AP

or user enters HUB867T-4#R[ER03] in column R, and it matches the part number in BA, I want a drop down of only column AS

the only way that I've been thinking about this working is have a column of all the part number qty's with a separator between them like this:

162
324
486
-----
224
448
672

for all the column Q cells which need that input...and just scroll through them until I get the correct number I need

 

[sijpie]

It still is no clear. you write:

ie: user enters HUB284T-9[ER03] in column R, and it matches the part number in BA, I want a drop down of only column AP

or user enters HUB867T-4#R[ER03] in column R, and it matches the part number in BA, I want a drop down of only column AS

So both part numbers appear in BA, but one gets it quantity from AP, the other from AS. Why? How can a formula decide?

It could be possible to add column BB in which for each partnumber it states which column to use. Then it will be very simple to achieve your goal.

 

[TBud1981]

the part numbers that are matching from column R and BA, it would take those matches and use the specific column based on that part number.
like I said...I don't know if that would be even possible as this seems to be pretty complex.

how would I achieve this based on the pallet column being in BB?

 

[sijpie]

By using =INDIRECT() in the source box for the validation list

The formula would be something like
=INDIRECT(XLOOKUP(R2,BA2:BA20,BB2:BB20)&"2:"&XLOOKUP(R2,BA2:BA20,BB2:BB20)&"17")

simplified spreadsheet:

What this formula does is the following (Let's look at cell Q2 in the sheet above):
with INDIRECT() you create an address (range)
Starting with the XLOOKUP : it looks up the value of R2 (hub2) in the column BA. When it finds it it takes the value of the same row from column BB, which in this case is 'as'
The '&' in the formula stitch the components together. So the formula for Q2 becomes
=INDIRECT(as2:as17)
So excel interprets that as the range for validation is AS2:AS17

For your case of course you need to adjust the row numbers in the formula.

You can make the formula more efficient by using the LET() function. This is because the formula now looks up the value twice. The LET() function lets you give a name to a subfunction, so you can use this name in the rest of the formula, not having o repeat the sub function.

=LET(Pal,XLOOKUP(R2,BA2:BA20,BB2:BB20),INDIRECT(Pal&"2:"&Pal&"17"))

What it says is: Let 'Pal' be 'XLOOKUP(R2,BA2:BA20,BB2:BB20)', then carry out the calculation of 'INDIRECT(Pal&"2:"&Pal&"17")'

This approach is super flexible, because you can have more pallet number columns such as AP and AS, and just refer to these in column BB for the appropriate part numbers in column BA

 

[TBud1981]

Thank you very much for this!