SOLVED
Home

Conditional Data Validation List Help

%3CLINGO-SUB%20id%3D%22lingo-sub-326580%22%20slang%3D%22en-US%22%3EConditional%20Data%20Validation%20List%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326580%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Cell%20K11%2C%20I%20have%20a%20drop-down%20list%20of%20exercises.%20In%20Cell%20L10%2C%20I%20want%20a%20dropdown%20list%2C%20referencing%20K11%20found%20in%20another%20sheet%20%22New%20Exercise%20List%22%20(seen%20in%20below%20image)%20and%20return%20the%20contents%20of%20cells%20E10%20through%20H10%20as%20a%20drop-down%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F71168i03488D0BA50EF665%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Screen%20Shot%202019-01-28%20at%209.31.44%20AM.png%22%20title%3D%22Screen%20Shot%202019-01-28%20at%209.31.44%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20in%20cell%20K11%2C%20the%20exercise%20'Reverse%20Lunge'%20was%20selected%2C%20I%20want%20the%20cell%20L10%20to%20reference%20'Reverse%20Lunge'%20in%20the%20other%20sheet%2C%20and%20return%20E10%20to%20H10%20as%20a%20drop-down%20list.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F71171i1659C1580FC40CA3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%202019-01-28%20at%209.37.58%20AM.png%22%20title%3D%22Screen%20Shot%202019-01-28%20at%209.37.58%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20is%20the%20most%20effective%20way%20to%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20all!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-326580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-328797%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Data%20Validation%20List%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-328797%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20same%20principle%20applies.%20If%20you%20have%20a%20range%20name%20called%20%22KB_Box_Squat%22%20for%20the%20dependent%20drop-down%2C%20you%20can%20also%20create%20a%20range%20name%20called%20%22KB_Box_Squat_Load%22%20that%20points%20to%20the%20type%20of%20load.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20the%20data%20validation%20for%20L11%20you%20now%20need%20to%20use%20indirect%20to%20construct%20that%20range%20name%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E%3Dindirect(substitute(K11%2C%22%20%22%2C%22_%22)%26amp%3B%22_Load%22)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIn%20words%3A%20use%20the%20value%20in%20K11%2C%20replace%20spaces%20with%20underscores%20and%20append%20the%20text%20%22_Load%22%20and%20treat%20that%20as%20the%20range%20name.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-327238%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Data%20Validation%20List%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-327238%22%20slang%3D%22en-US%22%3E%3CP%3ENow%2C%20in%20the%20original%20picture%20(also%20seen%20below)%20I%20want%20cell%20L11%20to%20also%20have%20a%20dropdown%20menu%20while%20still%20referencing%20K11%2C%20but%20this%20time%20returning%20the%20data%20set%20directly%20below%20my%20named%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20399px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F71318i73D51BEABC8F3A3C%2Fimage-dimensions%2F399x260%3Fv%3D1.0%22%20width%3D%22399%22%20height%3D%22260%22%20alt%3D%22Screen%20Shot%202019-01-28%20at%209.31.44%20AM.png%22%20title%3D%22Screen%20Shot%202019-01-28%20at%209.31.44%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20if%20Reverse_Lunge%20was%20selected%20in%20K11%20in%20the%20above%20image%2C%20I%20want%20the%20dropdown%20menu%20in%20L11%20to%20reference%20the%20line%20of%20data%201%20row%20below%20my%20original%20named%20range%20'Reverse_Lunge.'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20566px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F71319i4FC76C465E917205%2Fimage-dimensions%2F566x209%3Fv%3D1.0%22%20width%3D%22566%22%20height%3D%22209%22%20alt%3D%22Screen%20Shot%202019-01-28%20at%209.34.17%20AM.png%22%20title%3D%22Screen%20Shot%202019-01-28%20at%209.34.17%20AM.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20the%20original%20exercise%20will%20have%203%20dropdown%20menus%20referencing%20it%20and%20returning%203%20different%20lists.%20Would%20this%20be%20an%20offset%20function%20inserted%20into%20the%20indirect%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326792%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20Data%20Validation%20List%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326792%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Emaybe%20it's%20just%20your%20data%20sample%2C%20but%20it%20looks%20to%20me%20as%20if%20the%20values%20in%20columns%20E%20to%20H%20are%20the%20same%20for%20all%20the%20different%20exercises.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20you%20refer%20to%20is%20called%20dependent%20data%20validation.%20In%20essence%2C%20you%20need%20to%20create%20a%20named%20range%20called%20%22Reverse_Lunge%22%20and%20point%20it%20to%20E10%3AH10%2C%20the%20same%20for%20Split_Squat%2C%20Box_Squat%2C%20etc.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20you%20can%20use%20an%20Indirect%20function%20in%20the%20data%20validation%20dialog%2C%20like%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Dindirect(substitute(K11%2C%22%20%22%2C%22_%22)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThis%20will%20take%20the%20text%20in%20K11%2C%20substitute%20all%20spaces%20for%20underscore%20characters%20and%20look%20for%20a%20named%20range%20with%20that%20name.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20need%20more%20help%20setting%20this%20up%2C%20take%20a%20look%20ad%20Debra%20Dahlgliesh's%20tutorial.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.contextures.com%2FxlDataVal02.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.contextures.com%2FxlDataVal02.html%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Shawn French
Occasional 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
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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies