Data Validation Create Lists using data validation option

%3CLINGO-SUB%20id%3D%22lingo-sub-2769811%22%20slang%3D%22en-US%22%3EData%20Validation%20Create%20Lists%20using%20data%20validation%20option%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769811%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Dear!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20to%20develop%20a%20dropdown%20list%20in%20%22Data%20Validation%22%20Options%20which%20I%20can%20build%20using%20INDIRECT%20and%20OFFSET%20formula%2Ffunctions.%20However%2C%20I%20getting%20trouble%20to%20apply%20data%20validation%20in%20following%20situation.%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20I%20have%20to%20allow%20user%20to%20see%20two%20different%20lists%20in%20cell%20Q4%20based%20on%20the%20value%20of%20cell%20(say%20P4)--%20the%20values%20are%20%22Yes%22%20and%20%22No%22%3C%2FP%3E%3CP%3E2.%20If%20No%20is%20selected%20in%20cell%20P4%2C%20the%20Q4%20shows%20%22N%2FA%22%20in%20dropdown%20list.%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20And%20if%20Yes%20is%20selected%20in%20cell%20P4%2C%20the%20Q4%20shows%20a%20list%20of%20dynamic%20codes%20based%20on%20the%20value%20of%20cell%20B4.%20The%20B4%20cell%20has%20different%20districts--%20say%20Rawalpindi%2C%20Islamabad.%20The%20codes%20are%20dynamic%20in%20a%20sense%20that%20if%20user%20selected%20Rawalpindi-%20The%20codes%20lists%20comes%20in%20dropdown%20is%20R1%20till%20R10%20and%20if%20user%20selects%20Islamabad%2C%20the%20code%20should%20be%20I1%20till%20I10.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20to%20deal%20with%20this%20situation%3F%20Could%20anybody%20help%20me%20out%3F%3C%2FP%3E%3CP%3EPlease%20refer%20tab%20OP1.1%201.1.2%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2769811%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%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-2769814%22%20slang%3D%22en-US%22%3ERE%3A%20Data%20Validation%20Create%20Lists%20using%20data%20validation%20option%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2769814%22%20slang%3D%22en-US%22%3EI%20am%20using%20windows%2010%20and%20office%20365.%3C%2FLINGO-BODY%3E
New Contributor

Hi Dear!

 

I have to develop a dropdown list in "Data Validation" Options which I can build using INDIRECT and OFFSET formula/functions. However, I getting trouble to apply data validation in following situation. 

1. I have to allow user to see two different lists in cell Q4 based on the value of cell (say P4)-- the values are "Yes" and "No"

2. If No is selected in cell P4, the Q4 shows "N/A" in dropdown list. 

3. And if Yes is selected in cell P4, the Q4 shows a list of dynamic codes based on the value of cell B4. The B4 cell has different districts-- say Rawalpindi, Islamabad. The codes are dynamic in a sense that if user selected Rawalpindi- The codes lists comes in dropdown is R1 till R10 and if user selects Islamabad, the code should be I1 till I10. 

 

How to deal with this situation? Could anybody help me out?

Please refer tab OP1.1 1.1.2

1 Reply
I am using windows 10 and office 365.