SOLVED

Conditional Data Validation List Help

Copper Contributor

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. 

Screen Shot 2019-01-28 at 9.31.44 AM.png

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.

Screen Shot 2019-01-28 at 9.37.58 AM.png

 

What is the most effective way to do this?

 

Thank you all!

3 Replies
best response confirmed by Shawn French (Copper Contributor)
Solution

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.

 

Screen Shot 2019-01-28 at 9.31.44 AM.png

 

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.'

 

Screen Shot 2019-01-28 at 9.34.17 AM.png

 

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.

1 best response

Accepted Solutions
best response confirmed by Shawn French (Copper Contributor)
Solution

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

 

View solution in original post