# Is there a way to work around the INDIRECT function for dynamic named ranges?

1. ## Is there a way to work around the INDIRECT function for dynamic named ranges?

Hi, I have a workbook with dynamic named ranges. If I use = INDIRECT(A1&"_Name"), then it will fail #REF!, here A1&"_Name" gives the dynamic name.

I did some search on the internet and confirmed that INDIRECT() does not work for dynamic named ranges.

So what can I do for this case?  Register To Reply

2. ## Re: Is there a way to work around the INDIRECT function for dynamic named ranges?

Works for me
Insert the numbers 1 to 5 in A1:A5
Define this as a named range as "abc"
in C1 put SUM(A1:A5), result 15
in C2 put SUM(abc) normal defined range, result 15

Now
In B1 put "a"
in C3 put =SUM(INDIRECT(B1&"bc")) reference defined range using INDIRECT result 15

INDIRECT(A1&"_Name") will obviously produce an #REF error (perhaps unless it's a single cell) as will INDIRECT(A1:A5)
It's the what you do with that INDIRECT(...) that produces the result, e.g. SUM(INDIRECT(...))  Register To Reply