Data Validation List Using Array and Constant

%3CLINGO-SUB%20id%3D%22lingo-sub-2524637%22%20slang%3D%22en-US%22%3EData%20Validation%20List%20Using%20Array%20and%20Constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2524637%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3EI%20am%20working%20on%20doing%20a%20dependent%20data%20validation.%20I%20finally%20have%20gotten%20the%20dependent%20list%20working%20(thanks%20to%20this%20forum!)%20using%20this%20%22List%22%20criteria%3A%26nbsp%3B%3D'Dynamic%20Route'!%24B%2425%23%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20addition%20to%20this%20dynamic%20array%2C%20I%20would%20also%20like%20to%20always%20have%20the%20choice%20%22overall%22%2C%20no%20matter%20how%20the%20array%20changes.%20I%20can%20think%20about%20this%20fix%20in%20two%20different%20possible%20ways%2C%20but%20I'm%20not%20sure%20how%20to%20do%20either%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Have%20the%20%22overall%22%20included%20in%20the%20data%20validation%20formula%20as%20some%20sort%20of%20constant%3C%2FP%3E%3CP%3E2.%20include%20the%20%22overall%20in%20the%20dynamic%20array%20as%20a%20constant%20(although%20still%20allowing%20the%20rest%20of%20the%20array%20to%20auto-populate)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESome%20help%20would%20be%20wonderful!%20Thanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2524637%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%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2526717%22%20slang%3D%22en-US%22%3ERe%3A%20Data%20Validation%20List%20Using%20Array%20and%20Constant%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526717%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1097832%22%20target%3D%22_blank%22%3E%40dhazan29%3C%2FA%3E%26nbsp%3BSee%20the%20picture%20below.%20The%20example%20filters%20the%20second%20column%20based%20on%20values%20in%20the%20first%20PLUS%20the%20row%20that%20contains%20%22Overall%22%20in%20the%20second%20column.%20would%20that%20work%20for%20you%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-08%20at%2007.59.49.png%22%20style%3D%22width%3A%20417px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294219iF2B89255C403E2A7%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-08%20at%2007.59.49.png%22%20alt%3D%22Screenshot%202021-07-08%20at%2007.59.49.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi!

I am working on doing a dependent data validation. I finally have gotten the dependent list working (thanks to this forum!) using this "List" criteria: ='Dynamic Route'!$B$25#

 

In addition to this dynamic array, I would also like to always have the choice "overall", no matter how the array changes. I can think about this fix in two different possible ways, but I'm not sure how to do either:

 

1. Have the "overall" included in the data validation formula as some sort of constant

2. include the "overall in the dynamic array as a constant (although still allowing the rest of the array to auto-populate)

 

Some help would be wonderful! Thanks in advance!

1 Reply

@dhazan29 See the picture below. The example filters the second column based on values in the first PLUS the row that contains "Overall" in the second column. would that work for you?

Screenshot 2021-07-08 at 07.59.49.png