Dependent data validation question

[jbaich]

Hi all, i feel like i've watched a million youtube videos and read a million articles and they all seem sooo similar to what i'm trying to accomplish, but i just can't quite get there...

Here's the situation; Excel 2016, will be a shared workbook so no tables allowed, workbooks will likely have between 3,000 and 10,000 rows, don't have permissions to download xl2BB... (work computer)

Sheet 1 is basically the control sheet, one row per unique id. Sheet 2 is some raw data, many records per id.

I would like to be able to use data validation in my control sheet based on the unique id for the row to list the relevant data from sheet 2. Example below...

Control sheet: (sheet 1)

Unique ID	Task	Due Date
1	basically index and match or vlookup from sheet 2 based on unique id and due date (adjacent right) criteria from sheet 1... would be super sweet if this could also be validation list and selection from this list would return due date (bidirectional)... asking a lot though i think...?	Would like to get data validation to list due dates for this unique id from sheet 2 in order of earliest to latest, so the validation list here would be: (from example sheet 2 below) 02-03-2020 05-03-2020 15-04-2020
2	same as above	same as above
3	same as above	same as above

Raw Data: (sheet 2)

Unique ID	Task	Due Date (dd-mm-yyyy)
1	Send letter	02-03-2020
1	Follow up Call	05-03-2020
1	File Report	15-04-2020
2	similar to above	you get the idea

so, i'm trying to figure out how to use named ranges and data validation so that the Task and Due Date cells in sheet 1 will drop down list the dates from sheet two and the associated task... in a perfect world, the two columns would work in both directions, ie. you choose a task via validation list from sheet 1 and the adjacent Due Date value updates or you choose a Due Date and the associated task is displayed... that might be too much to ask, so i'd be thrilled if i could prioritize the date validation and have the task value populated from that selection... i figure if i sort sheet 2 by earliest and filter out dates prior to today, i should get the desired sort order inherently?

any assistance would be very much appreciated!

 

[Dave87]

Have you had any luck with this? I've posted a similar question to How to do return multiple lookups within 1 cell without any luck so far

 

[jbaich]

Have you had any luck with this? I've posted a similar question to How to do return multiple lookups within 1 cell without any luck so far

Not really... I’m thinking I’ll try a combo of offset,index,match and countif , but I’m guessing it will slow things down a lot, so not sure if it will end up being successful... wish I had access to all the new functions...

 

[Akuini]

Hi, jbaich & Dave87

I might have an idea, but it involves vba and it's a bit complex.
Here's an example of 3 dependent data validations using vba:

example

it's not what you really want but let me know if you're interested in this method, I might amend the code to suit your need.

 

[Dave87]

Hi @jbaich Peter was very helpful in providing a fix to my post above, you should have a look at it if youre still stuck on this

 

[jbaich]

Hi @jbaich Peter was very helpful in providing a fix to my post above, you should have a look at it if youre still stuck on this

Thanks Dave, but I’m working in an older version of excel without those new functions and now that I’ve played with this for a bit, I’m not sure if the solution I thought I needed is actually what I needed... work in progress still, but thanks for the heads up on your solution!