Dependant dropdown in a table searching from between 2 date values

[rhysmcculloch]

Hi Everyone,

I have been trying to get this to work for a few days straight and i'm stumped. I'm trying to create a dropdown list inside a table that searches another table nearby fo all the sets that fall if the date logged is between the start and end date for that set.

I have attached an image of an example, i had an issue tying to get the XL2BB to work.

For example, if on day #1 entered with the date 2/06/2021 in the day_log table, i want the "set" column to have a dropdown list that comes from the "set_goals" table, with a list of the available sets that the logged date falls between. For for this example, the 2/06/2021 will fall between the start and end date of only the first set in the "set goals" table, so the drop down list would only show the corresponding "set 1". For the entries that are logged as 14/08/2021 & 15/08/2021, there would be 2 options available in the "set goals" table as there are 2 options that those dates fall between, so the dropdown list would only display the corresponding 2 values there, "set 1" (for that time period" & "set 2").

The goal is to use that to autofill the values section in the log table depending on the selected set from that dropdown list. e.g., on the day logged as "15/08/2021", there would be 2 available options int the dropdown ("set 1" & "set 2"), and then selecting "set 2" from the dropdown will lead the values columns to automatically lookup their corresponding values: 1500, 100, 1000, 700.

I really hope this makes sense! I have been fighting with it and looking up different ways to do it and I cannot get it to work. Any help would be greatly appreciated!

Thankyou!

 

[jasonb75]

I think that you might struggle to do this with dependent dropdowns / data validation. The dependent lists have to be located in a sheet somewhere, you can not create them on the fly using formulas.

This means that effectively you would need to carry out the unpractical task of manually creating a list for each date that you enter into column A.

If the use of vba is an option then using a cell selection change event to create the validation list when you click on a cell in column B is the only thing that comes to mind which might work.

 

[Marcelo Branco]

With this small data sample, maybe...

Select B2:B6
Data > Data Validation > List
Source: =OFFSET(INDEX(J$2:J$4,MATCH(1,($A2>=$H$2:$H$4)*($I$2:$I$4>=$A2),0)),,,COUNTIFS($H$2:$H$4,"<="&$A2,$I$2:$I$4,">="&$A2))

Remark: using structured references in data validation is complicated (requires INDIRECT), so I used cell references.

M.

 

[Marcelo Branco]

To get the values in columns C:F
C2
=IFERROR(INDEX(set_goals[Value1],AGGREGATE(15,6,(ROW(set_goals[Value1])-MIN(ROW(set_goals[Value1]))+1)/((day_log[@[Date]:[Date]]>=set_goals[[Start Date]:[Start Date]])*(day_log[@[Date]:[Date]]<=set_goals[[End Date]:[End Date]])*(set_goals[[Set]:[Set]]=day_log[@[set]:[set]])),1)),"")

drag to the right and down (not Ctrl+C / Ctrl+V)

M.