I have created two workbooks in Excel. The workbooks reside in separate folders in SharePoint, both folders being within the same 'Shared Documents'. One workbook looks up a lot of its data from the other using the VLOOKUP function. As the files are stored on SharePoint, the file reference for the VLOOKUP is a URL to the SharePoint location so, typically, the function would be as follows:
=VLOOKUP($B3,'URL/[WORKBOOK.xlsm]SHEET'!$A:$BB,C$1,FALSE)
The forum has required me to simplify the above as it won't let me use a link, but this is the structure of the lookup. The worksheet contains some 24,000 of these references and they all work perfectly for me, without fail. However, another user, accessing these worksheets from another location, keeps finding that these functions are being corrupted. When inspected, all of the cells containing this function have changed to:
=VLOOKUP($B3,#REF!$A:$BB,C$1,FALSE)
In other words, the whole URL has been replaced with #REF! This happens to all 24,000 cells containing the lookup function. The only way to correct this is to manually re-enter the formula in each and every cell (fortunately, I've been able to develop some VBA code to do this!).
There is no evidence that anything has changed or disappeared within the source workbook... indeed, I have never been able to reproduce this error when using it myself. Even if there were to be some problem obtaining the lookup data from the source workbook, I would expect Excel to just return a #REF! error, but not to actually modify the function itself. I have searched the internet extensively but can find no other instances of Excel making changes to cell contents in this way... it's peculiar!
Has anyone else ever experienced issues of this nature and, if so, any ideas as to what's going on?
Thank you in advance
Mike
Bookmarks