Dependent dropdown list using numbers (dates) instead of letters

[tabbytomo]

Good afternoon everyone!

I've got a weird one that I can't find anything for, nothing to even read up on so hoping one of you wizards can help! I'm simply wanting to make a dependent dropdown list where a date is chosen in one cell, then in the next cell a series of (numerical) values will be available to choose from. Slightly more difficult is that there is one alphabetical option that's required as well, so it would be a mix of alphabetical and numerical values.

So in sheet1, A2, choose an Expiry Date or N/A Part Box. Then in B2 (Batch Number), there's a choice of values depending on what was chosen in A2. The lists are on Sheet2.

If N/A Part Box was chosen in A2 (Expiry Date), then N/A Part Box is the only option available in B2 (Batch Number). However, if 30.04.22 was chosen in A2, then the options in B2 would be 916005 and 14019. If 30.06.24 was chosen, the only option in B2 would be 136991.

If we considered this question 1, I plan on expanding it and adding further questions and each will have a variable amount of possible choices for each Expiry and Batch Number.

Question one on Sheet1:

My list of values on Sheet2:

No matter what I try, I recieve errors because the defined name of choices are numerical. Would really appreciate any help!

 

[Toadstool]

Hi TabbyTomo,

Does this do what you want?

TabbyTomo.xlsx
	A	B	C
1	N/A Part Box	30.04.22	30.06.24
2	N/A Part Box	916005	136691
3		14019
Sheet2

TabbyTomo.xlsx
	A	B
1	Expiry Date	Batch Number
2	30.04.22	14019
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet2!$A$1:$C$1
B2	List	=OFFSET(Sheet2!$A$1,1,MATCH(A2,Sheet2!$A$1:$C$1,0)-1,COUNTIF(OFFSET(Sheet2!$A$1,1,MATCH(A2,Sheet2!$A$1:$C$1,0)-1,999),"<>"))

 

[tabbytomo]

Hi TabbyTomo,

Does this do what you want?

TabbyTomo.xlsx
	A	B	C
1	N/A Part Box	30.04.22	30.06.24
2	N/A Part Box	916005	136691
3		14019
Sheet2

TabbyTomo.xlsx
	A	B
1	Expiry Date	Batch Number
2	30.04.22	14019
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet2!$A$1:$C$1
B2	List	=OFFSET(Sheet2!$A$1,1,MATCH(A2,Sheet2!$A$1:$C$1,0)-1,COUNTIF(OFFSET(Sheet2!$A$1,1,MATCH(A2,Sheet2!$A$1:$C$1,0)-1,999),"<>"))

Works as far as I can tell, I'll have to have a play and expand it's use to know for sure, just picking it apart now as that's a little beyond me! Thank you ever so much.

 

[tabbytomo]

Hi TabbyTomo,

Does this do what you want?

TabbyTomo.xlsx
	A	B	C
1	N/A Part Box	30.04.22	30.06.24
2	N/A Part Box	916005	136691
3		14019
Sheet2

TabbyTomo.xlsx
	A	B
1	Expiry Date	Batch Number
2	30.04.22	14019
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet2!$A$1:$C$1
B2	List	=OFFSET(Sheet2!$A$1,1,MATCH(A2,Sheet2!$A$1:$C$1,0)-1,COUNTIF(OFFSET(Sheet2!$A$1,1,MATCH(A2,Sheet2!$A$1:$C$1,0)-1,999),"<>"))

I've got one further question if you don't mind? I'm having a difficult time understanding the formula, for example, all i'm trying to do is apply that to a working sheet, where A2 on Sheet1 is now C17, B2 now D17 and I can't get it working correctly. Any guidance would be amazing.

 

[Toadstool]

You're welcome.

Let me break it down.

The Expiry Date dropdown is simple enough as it's just offering any of the headers in row 1 of Sheet2, so the Data Validation List is just =Sheet2!$A$1:$C$1

The Batch Number dropdown is more complex.

OFFSET provides a cell or range of cells based on:
reference - the base of the offset
rows - rows to offset from that base
cols - columns to offset from the base
[height] - optional, number of rows in the range of cells
[width] - optional, number of columns in the range of cells

We want to supply a range of cells being the list from row 2 of sheet2 for the Expiry Date selected in C17. Let us assume 30.04.22 was selected.

We give it the base reference of Sheet2!$A$1 but we know the list starts in row 2 so we give it a rows of 1 so it starts in A2.
To get the cols we MATCH the selected Expiry Date of 30.04.22 in C17 with the Sheet2 headings in A1 to C1 with an exact match. In this case 30.04.22 is the second so returns a 2, but if we offset $A$1 by 2 we'll get column A3, so we subtract 1 from the MATCH return. That gets us to
=OFFSET(Sheet2!$A$1,1,MATCH(C17,Sheet2!$A$1:$C$1,0)-1

The next OFFSET parameter is the height (and last we'll use because we don't need a width as our dropdown is always in 1 column).
The height will be how many rows of the list to offer.
The COUNTIF will count how many cells are not blank between row 2 and row 999 of the appropriate column. So now we're back to another OFFSET to again find the selected column but now we use a height of 999 so the COUNTIF returns, for 30.04.22, the number 2.

If I move the input dropdowns to C17 and D17 the formula changes to

=OFFSET(Sheet2!$A$1,1,MATCH(C17,Sheet2!$A$1:$C$1,0)-1,COUNTIF(OFFSET(Sheet2!$A$1,1,MATCH(C17,Sheet2!$A$1:$C$1,0)-1,999),"<>"))

...and if I resolve the MATCH and COUNTIF statements the the OFFSET for 30.04.22 would look like
=OFFSET(Sheet2!$A$1,1,1,2) which would be the range Sheet2!B2:B3 so the Data Validation list dropdown would offer two cells, 916005 and 14019.

I hope that explains it OK.

TabbyTomo.xlsx
	C	D
16	Expiry Date	Batch Number
17	30.04.22	14019
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
C17	List	=Sheet2!$A$1:$C$1
D17	List	=OFFSET(Sheet2!$A$1,1,MATCH(C17,Sheet2!$A$1:$C$1,0)-1,COUNTIF(OFFSET(Sheet2!$A$1,1,MATCH(C17,Sheet2!$A$1:$C$1,0)-1,999),"<>"))