+ Reply to Thread
Results 1 to 4 of 4

Peculiar #REF! error when using VLOOKUP to a SharePoint location

  1. #1
    Registered User
    Join Date
    05-31-2020
    Location
    Norfolk, England
    MS-Off Ver
    365
    Posts
    22

    Peculiar #REF! error when using VLOOKUP to a SharePoint location

    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
    Last edited by Apollo1969; 05-31-2020 at 06:06 AM.

  2. #2
    Registered User
    Join Date
    05-31-2020
    Location
    Norfolk, England
    MS-Off Ver
    365
    Posts
    22

    Re: Peculiar #REF! error when using VLOOKUP to a SharePoint location

    Welcome to the forum.

    Is either of you synchronising locally? If you are, then user names come into play.
    Hi AliGW, I'm not sure why your reply isn't showing in the forum, but received a copy via email!?!?

    I'm not aware that either of us is synchronising locally, but must admit I didn't set up Sharepoint on either PC. Is there an easy way to tell?

    Mike

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,338

    Re: Peculiar #REF! error when using VLOOKUP to a SharePoint location

    That’s very weird! I definitely posted to ask that question ...

    Are you able to access the SharePoint folders as folders in Explorer on the PC, or are you accessing the files solely via the browser?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    05-31-2020
    Location
    Norfolk, England
    MS-Off Ver
    365
    Posts
    22

    Re: Peculiar #REF! error when using VLOOKUP to a SharePoint location

    Yes, I access the SharePoint folders using Internet Explorer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ChDir location, using a http link to sharepoint
    By penfold1992 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-16-2022, 06:06 AM
  2. [SOLVED] Macro to Save Excel File to a SharePoint location and then e-mail that location in Outlook
    By NickyHavey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2016, 09:27 AM
  3. Very peculiar error
    By samot79 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-29-2014, 04:41 PM
  4. Tweak VBA to Move SharePoint Files to a Folder in Same Location
    By n_lindsey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2014, 03:21 PM
  5. Dynamic vlookup to a remote location in sharepoint
    By Robby1985 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-27-2012, 03:13 PM
  6. peculiar error with Range() object
    By Jeff in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2006, 04:40 PM
  7. Replies: 7
    Last Post: 04-01-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1