98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
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
Resolved · High Priority · Version 2016
Wed 26 Sep 2018, 10:29Delegate Tegan said...
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!
Wed 26 Sep 2018, 13:36Trainer Jens said...
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
Thu 27 Sep 2018, 12:20Trainer Sarah said...
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
Thu 27 Sep 2018, 14:22Delegate Tegan said...
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
Thu 27 Sep 2018, 17:16Trainer Wendy said...
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.pdfThu 4 Oct 2018: Automatically marked as resolved.
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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Line breaks in a cellYou 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. |
