Dependent drop down after 1st line

%3CLINGO-SUB%20id%3D%22lingo-sub-1643350%22%20slang%3D%22en-US%22%3EDependent%20drop%20down%20after%201st%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643350%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22lia-message-subject-wrapper%20lia-component-subject%20lia-component-message-view-widget-subject-with-options%22%3E%3CDIV%20class%3D%22MessageSubject%22%3E%3CDIV%20class%3D%22MessageSubjectIcons%20%22%3E%3CH1%20id%3D%22toc-hId-527417225%22%20id%3D%22toc-hId-527417229%22%20id%3D%22toc-hId-527417229%22%20id%3D%22toc-hId-527417229%22%20id%3D%22toc-hId-527417229%22%3E%3CSPAN%20class%3D%22lia-message-unread%20lia-message-unread-windows%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fresources-and-community%2Fdependent-drop-down-lists-with-multiple-words-replicating-the%2Fm-p%2F1641647%22%20target%3D%22_blank%22%3EDependent%20drop%20down%20lists%20with%20multiple%20words%20-%20replicating%20the%20validation%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FA%3E%3C%2FSPAN%3E%3C%2FH1%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22lia-message-body%20lia-component-message-view-widget-body%20lia-component-body-signature-highlight-escalation%20lia-component-message-view-widget-body-signature-highlight-escalation%22%3E%3CDIV%20class%3D%22lia-message-body-content%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20having%20trouble%20with%20dependent%20drop%20down%20lists.%20I%20managed%20to%20follow%20a%20tutorial%20here%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Fexcel-dependent-drop-down-lists-multiple-words-spaces%2F%23%3A~%3Atext%3DGo%2520to%2520the%2520cell%2520where%2Coption%2520for%2520the%2520first%2520dropdown%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.xelplus.com%2Fexcel-dependent-drop-down-lists-multiple-words-spaces%2F%23%3A~%3Atext%3DGo%2520to%2520the...%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20managed%20to%20get%20the%20dependent%20data%20validation%20working.%20However%2C%20it%20will%20only%20work%20on%20the%20first%20row%20(where%20i%20originally%20inserted%20the%20data%20validation%20formula).%20When%20I%20attempt%20to%20copy%20the%20validation%20down%20the%20sheet%2C%20it%20only%20copies%20what%20is%20in%20the%20first%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20example%2C%20the%20dependents%20are%201.%20a%20list%20of%20standards%2C%20and%202.%20a%20list%20of%20standard%20categories.%26nbsp%3B%3C%2FP%3E%3CP%3ESo.%20Line%201%20(where%20i%20wrote%20the%20original%20data%20validation%20formula)%20cell%202C%20i%20select%20from%20a%20list%20of%20standards%2C%20and%20then%20in%202D%20I%20get%20a%20list%20of%20standard%20categories%2C%20based%20on%20what%20i%20selected%20in%202C.%20However%2C%20when%20I%20attempt%20to%20copy%20the%20data%20validation%20down%2C%20I%20am%20able%20to%20select%20from%20the%20list%20of%20standards%20in%203C%20just%20as%20above%2C%20but%20in%20cell%203D%20It%20just%20copies%20the%20standard%20categories%20from%202C%20(minus%20the%20one%20already%20showing%20in%20the%20row%20above.)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20really%20struggling%20to%20understand%20why!!%20Can%20anyone%20help%3F%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1643350%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-1643509%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20after%201st%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F784657%22%20target%3D%22_blank%22%3E%40heatherguest%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUsing%20of%20absolute%20and%20relative%20references%20in%20data%20validation%20formula%20is%20not%20correct%2C%20if%20change%20on%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX('Background%20Forms%20HM'!%24A%242%3A%24I%2425%2C%2CMATCH(%24C2%2C'Background%20Forms%20HM'!%24A%241%3A%24I%241%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eit%20will%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20393px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F216985i393E3063CE7D9613%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1643607%22%20slang%3D%22en-US%22%3ERe%3A%20Dependent%20drop%20down%20after%201st%20line%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1643607%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20Thank%20you%20so%20much!!!%20i%20knew%20I%20was%20doing%20something%20wrong%2C%20I%20just%20could%20not%20figure%20it%20out!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, 

 

I am having trouble with dependent drop down lists. I managed to follow a tutorial here: 

 

https://www.xelplus.com/excel-dependent-drop-down-lists-multiple-words-spaces/#:~:text=Go%20to%20the....

 

and managed to get the dependent data validation working. However, it will only work on the first row (where i originally inserted the data validation formula). When I attempt to copy the validation down the sheet, it only copies what is in the first row.

 

So for example, the dependents are 1. a list of standards, and 2. a list of standard categories. 

So. Line 1 (where i wrote the original data validation formula) cell 2C i select from a list of standards, and then in 2D I get a list of standard categories, based on what i selected in 2C. However, when I attempt to copy the data validation down, I am able to select from the list of standards in 3C just as above, but in cell 3D It just copies the standard categories from 2C (minus the one already showing in the row above.) 

 

I am really struggling to understand why!! Can anyone help? 

3 Replies

@heatherguest 

Using of absolute and relative references in data validation formula is not correct, if change on

=INDEX('Background Forms HM'!$A$2:$I$25,,MATCH($C2,'Background Forms HM'!$A$1:$I$1,0))

it will be like

image.png

@Sergei Baklan  Thank you so much!!! i knew I was doing something wrong, I just could not figure it out! 

@heatherguest , you are welcome