Forum Discussion
Conditional Data Validation List Help
Hello,
maybe it's just your data sample, but it looks to me as if the values in columns E to H are the same for all the different exercises.
What you refer to is called dependent data validation. In essence, you need to create a named range called "Reverse_Lunge" and point it to E10:H10, the same for Split_Squat, Box_Squat, etc.
Then you can use an Indirect function in the data validation dialog, like
=indirect(substitute(K11," ","_")
This will take the text in K11, substitute all spaces for underscore characters and look for a named range with that name.
If you need more help setting this up, take a look ad Debra Dahlgliesh's tutorial. https://www.contextures.com/xlDataVal02.html
Hello,
maybe it's just your data sample, but it looks to me as if the values in columns E to H are the same for all the different exercises.
What you refer to is called dependent data validation. In essence, you need to create a named range called "Reverse_Lunge" and point it to E10:H10, the same for Split_Squat, Box_Squat, etc.
Then you can use an Indirect function in the data validation dialog, like
=indirect(substitute(K11," ","_")
This will take the text in K11, substitute all spaces for underscore characters and look for a named range with that name.
If you need more help setting this up, take a look ad Debra Dahlgliesh's tutorial. https://www.contextures.com/xlDataVal02.html
Now, in the original picture (also seen below) I want cell L11 to also have a dropdown menu while still referencing K11, but this time returning the data set directly below my named range.
For example, if Reverse_Lunge was selected in K11 in the above image, I want the dropdown menu in L11 to reference the line of data 1 row below my original named range 'Reverse_Lunge.'
Basically, the original exercise will have 3 dropdown menus referencing it and returning 3 different lists. Would this be an offset function inserted into the indirect function?
Thank you!
The same principle applies. If you have a range name called "KB_Box_Squat" for the dependent drop-down, you can also create a range name called "KB_Box_Squat_Load" that points to the type of load.
In the data validation for L11 you now need to use indirect to construct that range name:
=indirect(substitute(K11," ","_")&"_Load")
In words: use the value in K11, replace spaces with underscores and append the text "_Load" and treat that as the range name.
