Excel - Special Names in Dynamic Lists

I posted earlier about a better way for producing dynamic lists in Excel.  One of the “tricks” I used to simplify things was using the text values of the items in a primary list to identify a secondary list to use.

i.e.

Here we have three lists with the options set as named ranges called “Type”, “Fruit” and “Vegetable”.  Using data validation, drop down lists and Excel’s INDIRECT function means that if “Vegetable” is selected from a drop down linked to the “Type” list as a primary list, a related secondary list drop down will dynamically link to the “Vegetable” named range to validate the drop down.

This eliminates the need to do any complex lookups, but complex lookups look like the option when confronted with a primary list that contains entriess that would be invalid as names for a named range.

For example if we were looking at say UK regions and cities then an entry of “North Yorkshire” in the primary list would cause an issue as it contains a space which is an invalid character in an Excel name.

My solution is to replace the space in the naming of the range with an underscore.  This obviously won’t match to the entry in the primary list but we can use a quick substitution to accommodate this.

Simply put we might have something like in the data validation list entry for a cell linked to cell A2:

=INDIRECT(SUBSTITUTE(A2," ","\_"))

This would simply replace the spaces with underscores and provide the valid name for Excel to use.

If we’re using the full validation option from my previous post on data validation, then this substitute would be moved into the function of the working area.  So using the same example as the previous post:

Cell D4 would now contain the following function:

=IF(B4="","",SUBSTITUTE(B4," ","\_"))

Cell C4 would (still) contain the following for its data validation list source:

=INDIRECT(D4)

So we can still avoid complicated lookup functions and still keep things relatively simple and instantly readable.

Author: Stephen Millard
Tags: | excel |

Buy me a coffeeBuy me a coffee



Related posts that you may also like to read