I have come across a unique problem which has caused a challenging issue for us as we have had some wrong data presented to screen (and only discovered post review). Out of all the formula's on our worksheet (which are all inherently the same and look up the same source data), some formula's have worked and some haven't. What baffles me is that the incorrect returning result formula's are now operational and returning correct results today yet they have been untouched since yesterday. I have checked the source data and there are no issues. I am running Excel 2010.
As this is work related document I am unfortunately unable to post the workbook.
The written formula is;
=IF(ISNA(INDEX('\\network_path\[source_file.xlsx]Source Data'!$O$350:$O$410,MATCH($A1,'\\network_path\[source_file.xlsx]Source Data'!$A$350:$A$410,FALSE))),"No data",INDEX('\\network_path\[source_file.xlsx]Source Data'!$O$350:$O$410,MATCH($A1,'\\network_path\[source_file.xlsx]Source Data'!$A$350:$A$410,FALSE)))
A couple of formula's in this worksheet did return correct results. After some investigation the results that were presented to us for the 2 discovered culprit cells were;
Cell #1 - Result xyz 3 rows below the target cell
Cell #2 - Result xyz located in cell C143
Clearly it has completely failed.
Our method of updating these values is;
#1 - Saving the source_file.xlsx
#2 - Separately updating the data values (via the Data -> Edit Links option)
Could this be a memory leak? Could this be that the 'FALSE' attribute in the match function for these formula's has failed? Could it be that others may have failed?
Would appreciate any insight.