VBA - Update Data Validation list if checkbox is checked/unchecked

[Daniel_72]

Hi All,

Here is a tough question/problem I need help with:

I have one sheet (“DataVal”) with a lot of possible alternative for the user:

For example, in column A10:A16 I have mon, Tue, Wed, Thu, Fri, Sat and Sun with checkboxes linked to each cell. The idea Is that, if the checkbox for example mon, wed, sat is checked, it will update the data validation list that is in sheet MasterData in column E from row nr 16 and down to row 1000.

Later on, the Admin-user might want to uncheck or check new alternative in the checkbox list (sheet “DataVal”), and I want the data validation list in sheet “MasterData” E16:E1000 to reflect the changes.

I can’t use the list A10:A16 in sheet “DataVal” as a list for data validation because all alternative should not be possible for all different departments. Also, I don’t want any blanks alternative in the data validation list in sheet MasterData E16:E1000.

Does anyone have any suggestion, I have tried a lot of different solutions but I can’t get I to work as I want? Maybe some VBA Code would the trick……..

Thanks in advance

Daniel

 

[Yongle]

If Mon and Tue are ticked in DateVal .... does the DV list in MasterData become Mon, Tue ?
If Mon, Thu and Sun are ticked in DateVal .... does the DV list in MasterData become Mon, Thu, Sun ?
etc

Cells E16:E1000 in MasterData may already contain values when a different combination of days is ticked in DateVal
- some historic values may no longer meet the new DV criteria
- does that matter ?
- if so ... what should happen to those values

 

[Daniel_72]

If Mon and Tue are ticked in DateVal .... does the DV list in MasterData become Mon, Tue ?
If Mon, Thu and Sun are ticked in DateVal .... does the DV list in MasterData become Mon, Thu, Sun ?
etc

Cells E16:E1000 in MasterData may already contain values when a different combination of days is ticked in DateVal
- some historic values may no longer meet the new DV criteria
- does that matter ?
- if so ... what should happen to those values

Hi,
Yes, if for example Tue, Thu, Fri and Sat is ticked in sheet DateVal I would like the DV list of choices to make in MasterData become Tue, Thu, Fri and Sat.
I understand that some historic values may no longer meet the new DV Criteria and that doesn't matter at all, those values are okey to be left in the file and shall not be erased.

The workbook is for delivery planning and recalculate route drives for the driver. The function above is for change of days possible to deliver, and because the file is going to be used in different departments and can change over time, I would like the flexibility of have these options build in, in the file from the start.

Is it possible?


Again thanks in advance

Daniel

 

[Yongle]

1. You can do this by formula
- have a look this video
- try to make it work for you and if you cannot make it work, come back to this thread
- if using Excel 2010 or later use method2 to avoid using array formula
- watch the whole video regardless
- method1 explains some things that are also used in method2

 

[Yongle]

This is similar to the method used in the video, except that it uses a Workings sheet (which can be hidden) and a helper column
- amend to suit your own requirements

Sheet named DateVal
- with 7 checkboxes in A10 to A16 linked to cells A10 to A16 in sheet Workings

Sheet named Workings

Data Validation Source
=dRange

Named Range dRange RefersTo
=Workings!$E10:INDEX(Workings!$E$10:$E$16,COUNTIF(Workings!$E$10:$E$16,"?*"))
(dRange is a dynamic named range)

Count of cumulative TRUE values (C10 copied down)
=COUNTIF($A$10:A10,TRUE)

List of days without blanks (E10 copied down)
=IFERROR(INDEX(B10:B16,MATCH(ROWS($B$10:B10),C10:C16,0)),"")
(matches first found cumulative count value in range)

 

[Daniel_72]

This is similar to the method used in the video, except that it uses a Workings sheet (which can be hidden) and a helper column
- amend to suit your own requirements

Sheet named DateVal
- with 7 checkboxes in A10 to A16 linked to cells A10 to A16 in sheet Workings
View attachment 6462


Sheet named Workings

View attachment 6464



Data Validation Source
=dRange

View attachment 6460

Named Range dRange RefersTo
=Workings!$E10:INDEX(Workings!$E$10:$E$16,COUNTIF(Workings!$E$10:$E$16,"?*"))
(dRange is a dynamic named range)

Count of cumulative TRUE values (C10 copied down)
=COUNTIF($A$10:A10,TRUE)

List of days without blanks (E10 copied down)
=IFERROR(INDEX(B10:B16,MATCH(ROWS($B$10:B10),C10:C16,0)),"")
(matches first found cumulative count value in range)

Hi again,

Thanks a lot, it works fine. I had a bit of problem because I use excel 2016 SWE version and had to replace , with ; and had to use swedish name of the functions.
But no it work

 

[Yongle]

That is good news - thanks for the feedback