just4kix
asked on
INDEX/MATCH & External Data Links results failure (Advanced users only)
Hi,
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;
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
MATCH attribute combined with potential network lag
ASKER
Regarding the network comments - I think you're onto something as this combined with the above may be the root cause of the problem. The time between file save and when we refresh the links is usually tight. Programmatically, Excel takes a read-only source file for link refreshing. That said, one may question how valid Excel's status notification of 'OK' in the Data Links window actually is for data accuracy.
Replication also does occur on the network which also may add complexity with the given timeframe.
We've now done away with the external source and brought the worksheets into the same file. Fingers crossed...