#REF! error

[julhs]

Have been working on formulas for few days in Workbook1 that use IndexMatch to pull product data from another Workbook (Sourcebook) without any #REF! error issue, even if the source book was closed, using:

INDEX('C:\Users\Me\Documents\SourceBook,MATCH(E8,'C:\Users\Me\Documents\SourceBook,0),1)………

Now for some strange reason I’m getting #REF! errors in Workbook1 cells when I edit any formula in a Workbook1 cell that relates to the Sourcebook when its closed.

Can anyone suggest why I could do this previously without #REF error, but not now!!

 

[Alex Blakenburg]

Thank your for also providing some test data to put into the Closed Workbook.

I am afraid I just can't get your formula to fail, with either the workbook open or closed.
The only odd behaviour I had was with =formulatext, which needed me to hit F9 to get it to refresh after I closed the data workbook. You could try F9 on your sheet on the off chance that it fixes the #REF! error ( I won't be holding my breath though).

Unfortunately I am on MS 365 and your profile says you are on 2010 so I can't test whether it is specifically an issue with 2010.
In theory the formula length should be fine even for 2010. My folder path was even longer than yours and still worked fine but like I said I am on 365 so that doesn't prove it would work in 2010.

How I tested.
With the primary workbook open, I copied in your formula.
I opened my aaFert_&_Chem_Pricing.xlsm copied in your test data using the same Sheet name and range names.
In the primary workbook I replaced your file path with nothing in the formulas. Since I had the aaFert workbook open the formulas now worked.
I then closed aaFert and the formulas updated to show my folder path for aaFert.
Editing the formula with aaFert closed did not break the formula.

 

[julhs]

Hitting F9 didn’t help, but that won’t surprise you!

Just as a by the way, just tried editing E9 with aaFert closed, didn’t make any changes. Instead of using the “tick” in the formula bar or “Enter button” to close the formula off I used the “X” in formula bar and I DID NOT get #REF in E9 as in the past.

Now wondering if this issue is being caused by the structure of the arguments in E9, as problem is only with this one formula out of the thousands in my Workbooks. I could try; a) find a way to restructure the formula, b) change the layout of my aaFert & Chem Pricing Book in some way, c) live with it in the knowledge that the formula does do what I intended, but always remember about the #REF issue when editing E9.

Many thanks for all your help.

 

[Alex Blakenburg]

Sorry we couldn't find a solution for you. You might want to consider upgrading to a later version of Excel. 2010 is now very old. It may resolve your issue but apart from not being able to replicate the issue I have no way of knowing if it would.