Creating named range from cell values

%3CLINGO-SUB%20id%3D%22lingo-sub-2990087%22%20slang%3D%22en-US%22%3ECreating%20named%20range%20from%20cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2990087%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20some%20lists%20and%20each%20of%20these%20lists%20has%20sub-lists.%20I%20am%20trying%20to%20create%20conditional%20data%20validated%20cells%20i.e.%20the%20validated%20list%20on%20the%20second%20cell%20should%20change%20depending%20on%20the%20value%20selected%20in%20the%20first%20cell.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20111px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328403iF6BEF9823DFB58B8%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20realised%20that%20the%20easiest%20way%20to%20do%20this%20is%3C%2FP%3E%3COL%3E%3CLI%3Eby%20creating%20named%20ranges%20of%20the%20sublists%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328405iB2FEDD94617FB963%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3Ecreating%20a%20data%20list%20with%20the%20main-list%20and%20sub-list%20name%20on%20the%20worksheet%3C%2FLI%3E%3CLI%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20360px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328406i250D42C7D9E084CA%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3CP%3E%26nbsp%3Busing%20vlookup%20on%20the%20first%20value%20with%20the%20data-list%20as%20a%20range%3C%2FP%3E%3C%2FLI%3E%3CLI%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20721px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328408i3B3592939162FC4D%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3CSPAN%3EFinally%2C%20using%20the%20result%20of%20vlookup%20(the%20named%20range)%20though%20INDIRECT%20in%20the%20data%20validation%20window.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20576px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328409i331064634C8DAD1B%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3EEverything%20works%20fine%20but%20the%20problem%20is%2C%20it%20is%20really%20cumbersome%20to%20create%20a%20named%20range%20for%20all%20the%20sub-lists%20especially%20when%20there%20are%20hundreds%20of%20them.%20Since%20I%20already%20have%20the%20name%20and%20the%20range%20listed%20on%20the%20sheet%2C%20I%20was%20wondering%20if%20there%20is%20a%20way%20to%20create%20a%20named%20range%20automatically%20from%20this%20data%20instead%20of%20entering%20it%20manually%20in%20the%20name%20manager.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2990087%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2990181%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20named%20range%20from%20cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2990181%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F316660%22%20target%3D%22_blank%22%3E%40Gekkouga%3C%2FA%3E%26nbsp%3BI%20think%20you%20can%20do%20this%20without%20named%20ranges.%20The%20validation%20formula%20could%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DINDIRECT(INDEX(references%2CMATCH(selection%2Clist%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3Bwhere%20%22references%22%20is%20the%20array%20with%20applicable%20cell%20references%20for%20the%20sublists%2C%20%22selection%22%20is%20the%20list%20for%20which%20the%20sublist%20should%20be%20in%20the%20dropdown%20and%20%22list%22%20is%20the%20array%20with%20all%20the%20lists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20didn't%20go%20though%20the%20trouble%20of%20replicating%20your%20schedule%2C%20but%20I%20did%20make%20a%20small%20example%20that%20demonstrates%20the%20principle.%20Perhaps%20it%20helps%20you%20to%20implement%20it%20in%20your%20own%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20Attached%20a%20new%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I have some lists and each of these lists has sub-lists. I am trying to create conditional data validated cells i.e. the validated list on the second cell should change depending on the value selected in the first cell.

image.png

 

I realised that the easiest way to do this is

  1. by creating named ranges of the sublists
  2. image.pngcreating a data list with the main-list and sub-list name on the worksheet
  3. image.png

     using vlookup on the first value with the data-list as a range

  4. image.png

     Finally, using the result of vlookup (the named range) though INDIRECT in the data validation window.

    image.png

     

Everything works fine but the problem is, it is really cumbersome to create a named range for all the sub-lists especially when there are hundreds of them. Since I already have the name and the range listed on the sheet, I was wondering if there is a way to create a named range automatically from this data instead of entering it manually in the name manager.

 

 

1 Reply

@Gekkouga I think you can do this without named ranges. The validation formula could look like this:

 

=INDIRECT(INDEX(references,MATCH(selection,list,0)))

 

 where "references" is the array with applicable cell references for the sublists, "selection" is the list for which the sublist should be in the dropdown and "list" is the array with all the lists.

 

I didn't go though the trouble of replicating your schedule, but I did make a small example that demonstrates the principle. Perhaps it helps you to implement it in your own sheet.

 

Edit: Attached a new file.