Indirect formula not working

[YounesB3]

Maybe I'm not seeing clear, but I can't see what's wrong with my formula...:

=INDIRECT("'"&AZ13&"["&$D8&" "&$H8&" "&$F8&".xlsx"&"]Budget Summary Conv to USD'!$AD$"&IF($D8="P90","82","130"),TRUE)

I tried removing the quotes in 82 and 130, it doesn't work either way.

I even compared the result within the indirect formula using F9 with the good end result and they match.

I have a #REF ! error.

Any help is appreciated!!

 

[Greg Truby]

Kinda tough to diagnose. What I would do is to take your expression:

"'"&AZ13&"["&$D8&" "&$H8&" "&$F8&".xlsx"&"]Budget Summary Conv to USD'!$AD$"&IF($D8="P90","82","130")

and put that in another (helper ) cell and then drop that helper cell's address into the indirect. For example, let's say you have cell HLP1's formula as:

="'"&AZ13&"["&$D8&" "&$H8&" "&$F8&".xlsx"&"]Budget Summary Conv to USD'!$AD$"&IF($D8="P90","82","130")

Then you go with =indirect(HLP1)

That should help make debugging easier.

 

[YounesB3]

I found it! I copied each part one by one and it worked although the expression didn't change at all. I think there was a special character as a space.

 

[YounesB3]

Actually no, it works when the files is open. When I close it back, I have the REF error again....

Any idea what's going on?

 

[Greg Truby]

Actually no, it works when the files is open. When I close it back, I have the REF error again....

Any idea what's going on?

Yup. You're linking into another workbook. Have a look at what those references looks like (when they're just "hard wired", not the result of an INDIRECT() call) when the foreign workbook is open versus closed. See the difference? When the foreign wb is open, the reference does not include the path. However if the foreign workbook is closed, then the reference gets really long as it includes the workbook's path as well.

You'll could use an IFERROR() to trap for it, i.e. if the wb is open, use short address, if closed (and so you're getting a #REF error) use the long address which includes the path.

 

[YounesB3]

Yup. You're linking into another workbook. Have a look at what those references looks like (when they're just "hard wired", not the result of an INDIRECT() call) when the foreign workbook is open versus closed. See the difference? When the foreign wb is open, the reference does not include the path. However if the foreign workbook is closed, then the reference gets really long as it includes the workbook's path as well.

You'll could use an IFERROR() to trap for it, i.e. if the wb is open, use short address, if closed (and so you're getting a #REF error) use the long address which includes the path.

Okok! Thing is, I used the long address when the wb was opened and it was working with the long address.

Also, I will never have the wb open (it was only to try and debug the formula).

 

[Greg Truby]

Thing is, I used the long address when the wb was opened and it was working with the long address.

Those are my favorite situations where you unknowningly do something whilest debugging that then makes the debugging veer off into left field.

 

[YounesB3]

Those are my favorite situations where you unknowningly do something whilest debugging that then makes the debugging veer off into left field.

Hahaha yeah! But my problem still isn't solved though.

When the file is closed, even if I use the long address, I still get the REF error. Would you happen to know why?

 

[Greg Truby]

When the file is closed, even if I use the long address, I still get the REF error. Would you happen to know why?

*sigh* We should have checked this... A simple search would have revealed that you are far from the first person to note that INDIRECT() does not play well with closed workbooks. I looked over nine results/threads and I don't see where anyone has posted a true formula-based solution. The only solutions that I see are all VBA based either using Longre's add-in or Harlan's PULL() function or where someone wrote his own VBA-based solution.

 

[YounesB3]

*sigh* We should have checked this... A simple search would have revealed that you are far from the first person to note that INDIRECT() does not play well with closed workbooks. I looked over nine results/threads and I don't see where anyone has posted a true formula-based solution. The only solutions that I see are all VBA based either using Longre's add-in or Harlan's PULL() function or where someone wrote his own VBA-based solution.

Ugh... didn't want to go there, but I guess I'll write a code then. Thanks!