+ Reply to Thread
Results 1 to 9 of 9

VLOOKUP from online spreadsheet to local spreadsheet

  1. #1
    Registered User
    Join Date
    08-09-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    38

    Question VLOOKUP from online spreadsheet to local spreadsheet

    Hiya,

    I'm trying to complete an IFERROR/VLOOKUP formula to pull information from a spreadsheet saved on OneDrive, to another spreadsheet saved locally.

    I've done this before with no issues but Excel isn't liking the link...


    My take:

    =IFERROR(VLOOKUP(C2'https://omegaingredients-my.sharepoint.com/:x:/g/personal/link/edited/for/privacy'!$A$1:$S$500,9,0),"not found")

    My intention is for the formula to look at column C on the local spreadsheet (i.e it says 'Apple' on C2)

    Then go to the OneDrive spreadsheet and find 'Apple' in column C (let's say it's C4 in this example).

    Then I need the spreadsheet to look at column I4 on the OneDrive sheet which is the price for the Apple.

    The formula then needs to pull this information over to the local spreadsheet in I2.



    I can't attach the spreadsheet as it's for work purposes and includes intellectual property so hoping my explanation above works!

    Thanks!

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    Well, in the example, assuming you have copied and pasted it, you are missing a comma after the cell C2 reference.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    Also, your lookup table starts with column A, but your description says that you want to find Apple in column C, so you will need to adjust that too.

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    08-09-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    38

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    Whenever i put a comma in after the C2 reference it deletes the entire link & look up reference leaving the formula as:

    =IFERROR(VLOOKUP(C2,,9,0),"not found")

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    To quote MS:

    In its simplest form, the VLOOKUP function says:

    =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

    So, you need the comma after the cell reference. I don't know the format of a sharepoint link so I'll just have to take your word for that. I suggest you copy the link before you edit the formula and paste it back after adding the comma.

  6. #6
    Registered User
    Join Date
    08-09-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    38

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    It really doesn't like the link... :/

    I've tried $ signs, commas, [] around the link, apostrophise with and without ... and the bit the error flags up is continually the 'what I want to look up', and 'where I want to look for it':

    (C2,'https ..

    Hopefully someone can help with the format of a sharepoint link

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    Thought you said:
    I've done this before with no issues …

  8. #8
    Registered User
    Join Date
    08-09-2017
    Location
    England
    MS-Off Ver
    2016
    Posts
    38

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    I have, and I've copied the same formula this time round... it just keeps going back to the start of the link which i've not experienced before.

    I've tried the online sheet from both SharePoint and OneDrive to see if that makes a difference.

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: VLOOKUP from online spreadsheet to local spreadsheet

    Copy this formula from here and paste it into the appropriate cell of your local file:

    =IFERROR(VLOOKUP(C2,'https://omegaingredients-my.sharepoint.com/:x:/g/personal/link/edited/for/privacy'!$C$1:$S$500,7,0),"not found")

    Here are the changes (shown in red):

    =IFERROR(VLOOKUP(C2,'https://omegaingredients-my.sharepoint.com/:x:/g/personal/link/edited/for/privacy'!$C$1:$S$500,7,0),"not found")

    Hope this helps.

    Pete

+ 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. Replies: 1
    Last Post: 11-12-2019, 05:03 PM
  2. Local web form to excel spreadsheet
    By ChrisSoulier in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 04-01-2017, 03:57 PM
  3. Change VBA Code to do VLookup on External Spreadsheet and not Local Worksheet
    By tommagpieconsultants in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-05-2015, 10:58 AM
  4. Replies: 1
    Last Post: 10-02-2014, 08:06 PM
  5. [SOLVED] Insert online linked spreadsheet into other spreadsheet
    By gab037 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-03-2012, 04:05 PM
  6. Building an EVE Online Spreadsheet of my own
    By Fraser_01 in forum Excel General
    Replies: 9
    Last Post: 03-18-2012, 12:36 AM
  7. [SOLVED] How do I put a spreadsheet online?
    By [email protected] in forum Excel General
    Replies: 2
    Last Post: 02-16-2005, 06:22 AM

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