Forum Discussion
Conditional Data Validation List Help
Hello!
In Cell K11, I have a drop-down list of exercises. In Cell L10, I want a dropdown list, referencing K11 found in another sheet "New Exercise List" (seen in below image) and return the contents of cells E10 through H10 as a drop-down list.
For example, if in cell K11, the exercise 'Reverse Lunge' was selected, I want the cell L10 to reference 'Reverse Lunge' in the other sheet, and return E10 to H10 as a drop-down list.
What is the most effective way to do this?
Thank you all!
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
3 Replies
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.
