#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!!

 

[Candyman8019]

First off, It looks like your sourcebook is missing a file extension, ie sourcebook.xlsx

Second, without seeing the complete formula, it will be difficult for anyone to provide relevant information

 

[julhs]

Thanks Candyman.
File path I posted is a bear bones example of the actual file path that I’m using, not the actual one.
Actual one is fine as it does the IndexMatching, it is just this #REF issue that occurs when I edit the formula.

Have been looking at this again and wondering if I’m missing something obvious. I’ll have a go with it again tomorrow with a fresher head and come back to you if you don’t mind.

 

[Candyman8019]

It looks like the range portion is missing from the formula.

 

[julhs]

I developed and tested the formula in “Testbook”, all ok no #REF.
I then copied “Testbook” E8 formula into “Workbook1” and edited it so that it IndexMatched a different source book.
It worked for several days without the #REF error when editing E8 with (aaFert_&_Chem_Pricing.xlsm'!Chemical_Range), CLOSED, but yesterday the #REF issue started up.

For now I’m only going to post two formula.
First one from “Testingbook” DOES NOT cause #REF error when I edit the formula, when the source book/sheet ( [Book3.xlsm]Masterlist ) is CLOSED,

Testingbook formula in E8

=IF(ISERROR(INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),2)),"Not found", IF(ISTEXT(INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),2)),"ltr text", IF(ISTEXT(INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),3)),"Kg text", IF(INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),2)+INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),3)=0,"No size entry", IF(INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),2)=0,INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),3),IF(INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),3)=0,INDEX([Book3.xlsm]Masterlist!B3:E9,MATCH(E8,[Book3.xlsm]Masterlist!B3:B9,0),2),"Double size entry"))))))

Second one from “Workbook1” is where I’m getting the #REF error when editing formula, when source book/sheet (aaFert_&_Chem_Pricing.xlsm'!Chemical_Range), is CLOSED.

Workbook1 formula in E8

=IF(ISERROR(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),1)),"Not found", IF(ISTEXT(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)),"ltr text", IF(ISTEXT(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)),"Kg text", IF(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)+INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0,"No size entry", IF(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3)=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4),IF(INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),4)=0,INDEX('aaFert_&_Chem_Pricing.xlsm'!Chemical_Range,MATCH(E8,'aaFert_&_Chem_Pricing.xlsm'!Chemical_Name,0),3),"Double size entry"))))))

 

[Candyman8019]

The only difference I can see is that in the formula that works the workbook name is enclosed in square brackets and in your formula you’re using a single quote.

 

[julhs]

Yes that is a difference.
However I changed the whole of Workbook1 formula to mirror the format of the Testingbook, using the different source book.
But that just resulted in the formula throwing syntax errors when entered.

To put it mildly, I’m absolutely lost with this!!!

Probably of no relevance but this is all on a my home PC.

 

[Candyman8019]

The only other thing I could think of is to validate that your named ranges are the exact ranges you expect them to be.

 

[julhs]

Did think of that, so I the edit the whole Workbook1 E8 formula so it did NOT use a Named range, just the reference to the actual range.

Eg, aaFert_&_Chem_Pricing.xlsm>Chemical_2016!>$A$4:$L$85.

No joy!!

 

[Candyman8019]

Oh. I just noticed that you shared the formula you have in E8…which is trying to index/match on E8. It should probably be referencing a different cell.