Hello,
I am a beginner in Power Query, and my English is not perfect. So please be a little indulgent.
I am on Office 365 and on Sharepoint.
Here is a summary of the architecture:
- A QA folder (restricted access) including a "Name Management" file allowing the management of employee names and abbreviations.
- A general folder including the file accessible to all staff with the names and abbreviations "AME-...LST-QA-0004-P01-Belgium Hoeilaart Office - List of Initials"
- A General folder "Continuous Improvement/Master Templates - DO NOT USE/AXXMEYYY/03 Project Co-ord/03.10 List/AxxMExxx-.... Base Project.xls. This file contains the main data concerning a project and a sheet with drop-down menus. This folder contains the files currently being developed.
- When a file is finished it is saved in the folder "Template (BE)" following the same structure AXXMEYYY/..... It is therefore available to all staff and approved by QA.
- On the server there are all the AXXMEYYY projects with the same structure as in Sharepoint, we then copy the files from the sharepoint to the correct folder (project) on the server.
- Create from the "AME-...LST-QA-0004-P01-Belgium Hoeilaart Office - List of Initials" file (this file always remaining in the same place in the structure) a dynamic drop-down list of the names and abbreviations of the "Ops" staff by removing the "Left". Links to the "Name Management" file (restricted access) via vlookup or power query?I like xlookup but I have the same problem it only works in the same file, otherwise #Ref.
- Find in the Base Project file in the folder Master Template-Do not use" and in the Folder "Template (BE) this drop-down list this file always remaining in the same place in the structure (sharepoint to sharepoint) and there I get stuck for the first time.
- Then when we copy the file from the sharepoint to the folder it will be the same thing. The "links" should follow.
In summary, automatic link between different .xls files in sharepoint and from sharepoint to a network. I am sure it is possible but I can't find the means to achieve it. I hope I have been as clear as possible and again sorry for my English.
Hello,
See if you can find a solution from How to correct a #REF! error - Microsoft Support.
In your scenario I would use Power Query to get the data from the other workbooks. Check out the Get Data from OneDrive or SharePoint with Power Query blog article.
Hope you find a solution, always frustrating to get stuck.
Br,
Anders
Thanks a lot Anders
