vlookup using two workbooks

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Vlookup using two workbooks on SharePoint | Excel forum

Vlookup using two workbooks on SharePoint | Excel forum

resolvedResolved · High Priority · Version 2016

Tegan has attended:
Excel Intermediate course

Vlookup using two workbooks on SharePoint

I am trying to use a Vlookup using two separate Excel 2016 workbooks which are both on SharePoint. I successfully learnt how to use Vlookups using separate workbooks, but I want to know whether it is possible when they are on SharePoint, and how to do this?

For the table array, instead of typing in the external file name I instead dragged my mouse over the relevant table, and this automatically turned into a URL in the formula bar when I finished the formula and then selected it again to inspect it. This seemed to work.

However when I reopened the workbook later in Excel Online, there was an error message in a ribbon at the top saying "Links disabled: Links to external workbooks are not supported and have been disabled". And where my data should be, there are #REF! errors. When I open the workbook in Excel a dialogue box says there are external links and if I trust them I can update them, or work with the data I have. I selected 'update' but nothing changed.

How can I fix this? Or is it just not possible?

The workbooks really do need to be on SharePoint for my colleagues to access and update, they are not functional if they only work on my local computer. And I need this for a report I am preparing at the moment.

Thanks in advance for your help!

RE: Vlookup using two workbooks on SharePoint

Hi Tegan,

Thank you for the forum question.


https://stackoverflow.com/questions/27263188/linking-two-excel-worksheets-in-one-drive-sharepoint


https://social.msdn.microsoft.com/Forums/office/en-US/f94d25b6-749c-470c-837d-32bcf570bfd3/vlookup-to-look-at-a-excel-document-stored-on-sharepoint?forum=exceldev

Please see the links above.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Vlookup using two workbooks on SharePoint

Hi Tegan

Thanks for getting in contact with us again.

It definitely should be possible to do what you're trying, however any links you create between two files in Sharepoint will always be slightly fragile due to the nature of shared online workspaces and the different permissions.

I'd like to help problem solve your issue but need to know a bit more information.

- Are both of your files in the same folder in Sharepoint?
- And could you copy and paste the exact VLOOKUP formula you used here for me to have a look at?

If you let me know these things then I'll do my best to get to the bottom of the problem today for you.

Kind Regards,

Sarah
Excel Trainer

RE: Vlookup using two workbooks on SharePoint

Hi Sarah. Thank you for your help.

The two files are not in the same folder, one is in a subfolder of the other. But they are in the same root folder and I can easily keep them in the same folder if necessary.

Here is an example of the formula:

=VLOOKUP(G7,'https://cdp076.sharepoint.com/sites/communications/Media/Media coverage/Monthly coverage data/Master-publications-countries.xlsx'!Table1[#All],2,FALSE)

I used this first:

=VLOOKUP(G2,'Master-publications-countries.xlsx'!Table1[#All],2,FALSE)

But it automatically turned into the URL version above and broke, showing the #REF! errors.

It is being temperamental. It actually worked earlier, and then when I closed and reopened the file it was broken again. I don't understand what is wrong with it!

When I first typed out the formula I didn't know what to type so I selected the relevant table in the separate file and let Excel populate the formula bar that way. Perhaps that caused an issue?

Any help on making it work consistently would be much appreciated!

Thanks,
Tegan

RE: Vlookup using two workbooks on SharePoint

Hi Tegan

You will need to sync the folder from sharepoint onto your local pc. Your IT department will be able to help you with this as it's dependant on the permissions set inside your network.

A VLookup, unfortunately, is going to create more problems than solutions. You might have to create a link using Power Query. I have created a document with detailed instructions and attached it to this post for you to follow.

Please let me know if this resolves your problem


Kind regards
Wendy Canelas
Microsoft Office Trainer

Attached files...

Creating a query in excel - Sharepoint.pdf

RE: Vlookup using two workbooks on SharePoint

Hi Wendy,

Thanks for your help. Unfortunately I don't see any attachment - did you forget it or am I being blind?

Thanks,
Tegan

RE: Vlookup using two workbooks on SharePoint

Now worries the attachment is now showing!

Thu 4 Oct 2018: Automatically marked as resolved.

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Line breaks in a cell

You can control the line breaks for multiple-line headings or labels in your Microsoft Excel worksheet, just like you do in Microsoft Word. Here's how to do it.

Click the cell where you want the label or heading to appear.
Type the first line of information.
Press ALT+ENTER.

View all Excel hints and tips


Server loaded in 0.06 secs.