CODEWITHSUNDEEP
excelexcel-formulaexcel-indirect
A-K-
A-K-

Reputation: 73

Why does INDIRECT() not work with name references?

As far as my understanding goes, if you have value 5 in H4, there are two ways to reference that cell that both work:

=H4

and

=INDIRECT("H4")

I can't figure out a way to make this work with name references though. For example, if I have some data I'd like to calculate the sum of, I can do this:

=SUM(Main!SomeData)

but the other method...

=SUM(INDIRECT("Main!SomeData"))

suddenly does NOT work, and I get a #REF error. I need to be able to use a named range in my INDIRECT() for something more complex, but I can't wrap my head around why it won't work like this. Can anyone help?

Upvotes: 3

Views: 500

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

Your formula will work if Main is the name of the workbook (not the worksheet) in which the formula resides:

enter image description here

If the formula resides in a different workbook, (say Book1.xlsx), then both should be open for the formula to work.

Upvotes: 4

Related Questions