Referencing a named range by formula in data validation

Question

Hello wonderful people.

I have a series of named ranges, A_Colours, B_Colours, etc. These represent sections of a sorted column in a colour list, sorted by codes for the manufacturer that makes those colours. These are dynamic named ranges, constructed by COUNTA for how many colours have that mfr code. By my calculation, the list should be contiguous, since the column is sorted by code.

I know I can reference the ranges in Data Validation by simply setting =A_Colours as the list source (no quotes). This does work in this case.

However, I have a dropdown to control which A-M range I want to show in the child dropdown. So, e.g. dropdown in A1 shows A, B, C, D as options. Dropdown in B1 shows dropdown options relative to the chosen option in A1.

EXAMPLE: I select A from the list in A1. In cell B1, I want my dropdown to show all values from named range A_Colours.

I have tried all manner of ways to pass the text string A_Colours to the DV list source field, but all have failed. Either error in formula, or the range evaluates to an error, or it allows me to select only A_Colours from the dropdown.

Example 1: A1 choose A B1 DV box:

=INDIRECT("$A$1")&"_Colours"

This gives error "The list source must be a delimited list, or a reference to single row or column."

Could this be because the table list is sorted? I don't think so because the very first example above works.

Example 2: use helper cell in C1 to create text string "A_Colours", by formula: =$A$1&"_Colours"

Then in DV box for cell B1:

=INDIRECT("$C$1")

Please help me achieve this! Thank you.

[Edit] SOLUTION:

Create new sheet as directed below. Create header for each manufacturer ("A" in cell A1). Enter formula into A2 (as an array formula, Ctrl+Shift+Enter):

=INDEX(Colours, SMALL(IF((INDEX(Colours, , 1)=INDEX(MFR,MATCH(A$1,MFR[MFRName],0),2)), MATCH(ROW(Colours), ROW(Colours)), ""), ROWS($B$1:B1)), COLUMNS($A$1:$B1))

I adapted it a bit, but credit for this goes to https://www.get-digital-help.com/2009/09/28/extract-all-rows-from-a-range-that-meet-criteria-in-one-column-in-excel/

Apply the formula down to the last plus a few to allow for expansion. Apply the formula across to your last header.

Now you have lists which are definitely contiguous and single-column and not dynamic, so meet the stringent criteria for data validation.

Create a named range for each list, using the exact name keeps it simple but I chose to add "Name" to the end, e.g. Mfr1Colours.

Reference them in the DV source list field (for cell B2 in our original sheet) like: =INDIRECT($B$2&"Colours")

What this does: Looks in the Colours table: MFR Code Colour Code

Finds the first matching instance of the MFR Code in cell A1 of our original sheet ("A") from the Manufacturer table "MFR", which has columns: MFRName Code

Pulls out the row by the MATCH(ROW(Colours,Row(Colours),"").

Checks which row and column you want by the ROWS and COLUMNS at the end.

jbrezik jbrezik · Accepted Answer · 2018-04-12T13:28:45

If you have a spare, helper range, where you would make a simple codetable with 2 columns - Color_Reference, Color_Names and name it c_colors:

A A_Colour
B B_Colour
C C_Colour

You could use this in CV list configuration for dropdoon on cell B1:

=INDIRECT(VLOOKUP(A1;c_colors;2;FALSE))

Try to look at the examples here, it may help you as well: http://www.contextures.com/xlDataVal02.html

Referencing a named range by formula in data validation

1 Answers