Jan 28 2019 06:40 AM
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!
Jan 28 2019 12:00 PM
SolutionHello,
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
Jan 29 2019 06:25 AM
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!
Jan 31 2019 12:46 PM - edited Jan 31 2019 12:47 PM
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.
Jan 28 2019 12:00 PM
SolutionHello,
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