0

I wanted to reuse a named range, and tried to prepend an "S" to the list of values but it does not want to work for me despite seeing the correct values in the Name manager.

I have List_A as a named range with the following reference and data in those cells:

Sheet1!$H$2:$H$8

List_A
10M
15M
20M
25M
35M
45M
55M

When I use data validation with list, I set the reference line to:

=List_A

This works fine.

When I use data validation with list and I set the reference line to:

="S"&List_A

I get an error and it does not work

When I make a Named Formula List_S_A

="S"&Sheet1!$H$2:$H$8

I can see the list prepended with "S" in the Name Manager.

When I use Data Validation List and in the reference line I put the following:

=List_S_A

I get an error message and it does not work.

Why is it not working despite it being visible in Name Manager?

Is there a way to do this without VBA, and not duplicating the list in another column with the "S" prepended?

enter image description here

Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • I did not think [this was the same question](https://stackoverflow.com/questions/55025245/pass-array-variable-to-excel-named-range-for-data-validation-list?rq=1) I was asking, but it MAY be giving the same answer – Forward Ed Nov 09 '22 at 02:39
  • 1
    It's because the nested names don't work in Data Validation. If you have Office 365 you can workaround by storing the data somewhere in your workbook. For instance in `Sheet2!A1` use `="S"&List_A` and refer to `Sheet2!A1#` in the data validation. Or name `Sheet2!A1#` `List_S_A` if you want. – P.b Nov 09 '22 at 06:58

0 Answers0