Please help me identify error in Indirect formula

hawkeye_zzz
hawkeye_zzz used Ask the Experts™
on
If I type in a cell "=name_range_1" I get the data in name_range_1
If I type in a cell "=name_range_2" I get the data in name_range_2
If I type in a cell "=+name_range_1" I get the data in name_range_1
If I type in a cell "=name_range1+name_range_2" I get the two numbers added up. So far so good.

If I type in a cell "=indirect(A5)" and in A5 I type in  "=name_range_1" I get the data in name_range_1
BUT
If I type in a cell "=indirect(A5)" and in A5 I type in "=+name_range_1" I get a #REF! (just added a "+" at the start, as worked above
AND
If I type in a cell "=indirect(A5)" and in A5 I type in "=name_range_1+name_range2" I get a #REF! (just added a "+" at the start, as worked above)


Why does adding a "+" symbol mess up the indirect formula?
How can I fix this so I can add two or more ranges via the indirect method
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
=INDIRECT(A5)+INDIRECT(A6)

...assuming each of the name range names are in cells A5 and A5. If both are in A5 separated by a +, use this...

=INDIRECT(LEFT(A5, FIND("+", A5)-1))+INDIRECT(MID(A5, FIND("+", A5)+1, 999))

The reason why you can not use a single INDIRECT function is there are no circumstances where a + symbol would represent a range. INDIRECT does not evaluate an expression, but merely takes a string and converts it to its Range equivalent.
hawkeye_zzz

Author

Commented:
Thank you! very helpful and well explained.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial