Excel Reserved Name for Named Ranges

Question

I am looking to create named ranges to make linked data validation. But the names that I must have seem to be reserved. I must name the ranges L5, L6, L7, etc. Is there a way around the reserved name?

Here are my lists

I am looking to create linked drop-down lists. If the user selects L5 for a line, I want the drop down for machines to show the L5 machines, similarly for L6 and L7.

Here is where the lists should be shown.

I was going to use INDIRECT in the Data Validation for this. So I would have the range Lists!$E$2:$E$4 for the data validation list in column E the Data sheet. Then I would have INDIRECT(E6) to only show the named range L5 or L6 or L7. But Excel does not like these names. What is a way around this?

You can use (eg) "LIST_L5" and then `INDIRECT("LIST_" & E6)` You get the idea. — Tim Williams, Feb 24 '18 at 22:38
you cant use names that would be the same as a legitimate cell address and I am going to guess it can be equal to an excel function name either. — Forward Ed, Feb 24 '18 at 22:44
@TimWilliams your suggestion was the way to handle this. I thought of it after asking my question. Thanks. — user823527, Feb 24 '18 at 23:35

score 0 · Answer 1 · answered Feb 24 '18 at 23:39

The answer as Tim Williams pointed out was to add a string to the named ranges to get around the reserved name issue. Then could recreate the named range with concatenation in the INDIRECT statement.

So my named ranges are L5_Machines, L6_Machines, L7_Machines. Then in the data validation I have the formula:

=INDIRECT(E1&"_Machines")

And this works perfectly.

Excel Reserved Name for Named Ranges

1 Answers1