Dependant dropdown in a table searching from between 2 date values

rhysmcculloch

New Member
Joined
Aug 23, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!
 

Attachments

  • Screenshot (2).png
    Screenshot (2).png
    226.7 KB · Views: 11

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,020
Messages
5,744,359
Members
423,863
Latest member
teehexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top