Dynamic data validation lists populated from another cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2829200%22%20slang%3D%22en-US%22%3EDynamic%20data%20validation%20lists%20populated%20from%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829200%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20achieve%20two%20things%20which%20I%20presume%20are%20possible%20but%20I'm%20unsure%20of%20where%20to%20start.%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%20image-alt%3D%22Screen%20Shot%202021-10-09%20at%201.30.34%20pm.png%22%20style%3D%22width%3A%20345px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316303i85299DB26578C237%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screen%20Shot%202021-10-09%20at%201.30.34%20pm.png%22%20alt%3D%22Screen%20Shot%202021-10-09%20at%201.30.34%20pm.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3EWhat%20I%20am%20hoping%20to%20achieve%20is%20to%20populate%20the%20list%20(shown%20in%20cell%20C4)%20with%20the%20data%20entered%20in%20cell%20A3.%20What%20I%20don't%20know%20is%20how%20to%20make%20the%20A3%20string%20breakdown%20based%20on%20the%20comma%20delimiter%20so%20that%20it%20is%20not%20one%20long%20string%2C%20but%20rather%20results%20in%20a%20list%20at%20C4%2C%20as%20displayed%20in%20the%20screenshot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3ENote%3C%2FEM%3E%3A%20I%20have%20hardcoded%20the%20list%20shown%20in%20the%20screenshot%20as%20I%20wanted%20to%20show%20what%20I%20need%20to%20achieve.%20If%20I%20reference%20A3%20directly%20(%3D%24A%243)%2C%20my%20list%20only%20has%20one%20item%20-%20'Yes%2C%20No%2C%20I%20don't%20know'%2C%20opposed%20to%20the%20list%20displayed%20in%20the%20screenshot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20if%20this%20is%20possible%2C%20instead%20of%20hard%20coding%20A3%20as%20the%20reference%2C%20I%20want%20to%20read%20the%20value%20in%20B4.%20This%20would%20allow%20me%20to%20change%20the%20value%20of%20B4%20to%20point%20at%20any%20other%20cell%2C%20such%20as%20A6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20believe%20both%20these%20things%20are%20likely%20possible%20but%20Googling%20has%20not%20helped%20as%20it%20appears%20I'm%20searching%20for%20the%20wrong%20keywords.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20anyone%20who%20can%20help%20me%20with%20this.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2829200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2829518%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20data%20validation%20lists%20populated%20from%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829518%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1180292%22%20target%3D%22_blank%22%3E%40ben_n_yc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20don't%20think%20that%20is%20possible%2C%20or%20if%20it%20is%2C%20it'll%20be%20complicated.%3C%2FP%3E%0A%3CP%3EInstead%20of%20entering%20the%20options%20in%20a%20single%20cell%2C%20enter%20them%20in%20a%20range%20of%20cells%2C%20with%20one%20option%20per%20cell.%20Then%20set%20the%20source%20of%20the%20data%20validation%20list%20to%20that%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2829685%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20data%20validation%20lists%20populated%20from%20another%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2829685%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1180292%22%20target%3D%22_blank%22%3E%40ben_n_yc%3C%2FA%3E%26nbsp%3BFILTERXML%20can%20be%20used%20to%20split%20the%20comma%20separated%20values.%3C%2FP%3E%3CP%3EI%20prefer%20making%20them%20look%20like%20HTML%3C%2FP%3E%3CP%3E%3D%22%22%20%26amp%3B%20SUBSTITUTE(A3%3B%22%2C%22%3B%22%22)%20%26amp%3B%20%22%22%3C%2FP%3E%3CP%3E%3CEM%3EYes%20No%20I%20don't%20know%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20from%20there%20parse%20them%20to%20separate%20cells.%3C%2FP%3E%3CP%3E%3DFILTERXML(%22%22%20%26amp%3B%20SUBSTITUTE(INDIRECT(B4)%3B%22%2C%22%3B%22%22)%20%26amp%3B%20%22%22%3B%22%2F%2Ftd%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDIRECT(B4)%20is%20one%20way%20to%20redirect%20to%20different%20set%20of%20options.%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%20image-alt%3D%22bosinander_0-1633782878517.png%22%20style%3D%22width%3A%20494px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316314i29F9C2A54E6DBC2E%2Fimage-dimensions%2F494x126%3Fv%3Dv2%22%20width%3D%22494%22%20height%3D%22126%22%20role%3D%22button%22%20title%3D%22bosinander_0-1633782878517.png%22%20alt%3D%22bosinander_0-1633782878517.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am trying to achieve two things which I presume are possible but I'm unsure of where to start.

 

Screen Shot 2021-10-09 at 1.30.34 pm.png

 

What I am hoping to achieve is to populate the list (shown in cell C4) with the data entered in cell A3. What I don't know is how to make the A3 string breakdown based on the comma delimiter so that it is not one long string, but rather results in a list at C4, as displayed in the screenshot.

 

Note: I have hardcoded the list shown in the screenshot as I wanted to show what I need to achieve. If I reference A3 directly (=$A$3), my list only has one item - 'Yes, No, I don't know', opposed to the list displayed in the screenshot.

 

Then, if this is possible, instead of hard coding A3 as the reference, I want to read the value in B4. This would allow me to change the value of B4 to point at any other cell, such as A6.

 

I believe both these things are likely possible but Googling has not helped as it appears I'm searching for the wrong keywords.

 

Thank you in advance for anyone who can help me with this.

2 Replies

@ben_n_yc 

I don't think that is possible, or if it is, it'll be complicated.

Instead of entering the options in a single cell, enter them in a range of cells, with one option per cell. Then set the source of the data validation list to that range.

@ben_n_yc FILTERXML can be used to split the comma separated values.

I prefer making them look like HTML

="<tr><td>" & SUBSTITUTE(A3;",";"</td><td>") & "</td></tr>"

<tr><td>Yes</td><td> No</td><td> I don't know</td></tr>

 

and from there parse them to separate cells.

=FILTERXML("<tr><td>" & SUBSTITUTE(INDIRECT(B4);",";"</td><td>") & "</td></tr>";"//td")

 

=INDIRECT(B4) is one way to redirect to different set of options.

 

bosinander_0-1633782878517.png