Dynamic Drop Down Lists (data validation)

[Super_Bob]

Hi All,

I have need of a dynamic drop down list that is based off another table, the filtering for this is already provided for me by other columns, I just need to find a way of getting this to work (preferably on older versions of excell but from my searches and attempts at solving it I understand I may need to use newer functions such as textjoin).

I've attached an example spreadsheet, on the sheet I'm actually working with there are around 6 option columns and will end up with thosands of rows, the table I'm drawing from has over a thousand rows however should generally resolve to a drop down list of anywhere between 0-40 options so I need a scalable solution if possible!

Thanks in advance for any help you can give

Edit: Updated file to include some other results.

[Bo_Ry]

Please sort Sheet2 Column 1,2,3
Then Data validation list at D2

=OFFSET(Sheet2!$D$1,MATCH(A2&B2&C2,Sheet2!$A$2:$A$25&Sheet2!$B$2:$B$25&Sheet2!$C$2:$C$25,),,COUNTIFS(Sheet2!$A$2:$A$25,A2,Sheet2!$B$2:$B$25,B2,Sheet2!$C$2:$C$25,C2))

[Super_Bob]

Hi Bo_Ry,

Thanks for having a look, for some reason the drop down lists for column d are not working on mine (office 365 subscription, validation is in there and the drop down icon shows but no options appear), do you know why that might be?

I see where you've gone with it though as basically you are creating a siding reference box for the list, I'll see if I can adapt it to my main sheet, thank you!

[AliGW]

@Super_Bob

Please update your forum profile to Office 365 ('current' is not specific enough, as there are more than one 'current' versions). Thanks.

The drop-down is non-functional here, too.

[Super_Bob]

Hi AliGW,

I've updated my profile with my home and work versions now.

[AliGW]

Very thorough! Thanks - it's helpful to know.

[Bo_Ry]

Please try again

=OFFSET(Sheet2!$D$1,MATCH(A2&B2&C2,INDEX(Sheet2!$A$2:$A$25&Sheet2!$B$2:$B$25&Sheet2!$C$2:$C$25,),),,COUNTIFS(Sheet2!$A$2:$A$25,A2,Sheet2!$B$2:$B$25,B2,Sheet2!$C$2:$C$25,C2))

[Super_Bob]

Hi Bo_Ry,

The new version worked perfectly thank you! Also I've just been adapting it for my actual work sheet and your old version worked a treat (something to do with the office version on my work computer maybe), I did have to shorten the references by creating named areas on the second sheet as there seems to be a 256 char limit on the data validation field but that was easy enough!

Thank you for all the help