Dependent data validation question

jbaich

Board Regular
Joined
Nov 2, 2011
Messages
132
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 IDTaskDue Date
1basically 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
2same as abovesame as above
3same as abovesame as above

Raw Data: (sheet 2)
Unique ID
TaskDue Date (dd-mm-yyyy)
1Send letter02-03-2020
1Follow up Call05-03-2020
1File Report15-04-2020
2similar to aboveyou 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!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Apr 22, 2020
Messages
105
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

Board Regular
Joined
Nov 2, 2011
Messages
132
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,112,772
Messages
5,542,436
Members
410,552
Latest member
Yogesh977
Top