Hi
I am seeking advice to:
Take all data from multiple sheets in multiple workbooks that are located in our Sharepoint location into one worksheet in a workbook that will be in a location other than sharepoint. The new workbook/worksheet combined data will be used for reporting of progress (via dashboard or the likes) to the organisation.
From what I have read and understand this may need to be a manual copy & replace of the data on a defined frequency (say monthly).
Any tips, ideas or help would be appreciated.
Regards, Gary
Hi Gary,
Power Query can eat that job for breakfast. This link talks about how to get your data from SharePoint: https://support.office.com/en-us/article/Connect-to-a-SharePoint-List-Power-Query-3226f248-f8b3-4777-82ce-b4ed04dedaaf
You can then perform the necessary merging/appending of the various workbooks into one query and load the query to an Excel table or Power Pivot.
Then each month you can simply refresh the query and you're good to go.
There are too many steps to cover in the forum, but you can learn Power Query here.
Versions of Excel supporting Power Query: https://support.office.com/en-us/article/Where-is-Get-Transform-Power-Query-e9332067-8e49-46fc-97ff-f2e1bfa0cb16?ui=en-US&rs=en-US&ad=US&fromAR=1
Mynda
Hi Mynda
I will review the articles you have provided links to.
Thanks for your advice, can I come back if I meet any issues?
Gary
Sure, but unless you already know how to use Power Query I think you're going to need to learn how to use it first.
Mynda
Hi Gary,
I Think you also use some dedicated Tool which is specially made for Combine multiple workbooks. Like Synkronizer Excel Compare Tool. Try This: https://www.synkronizer.com/compare-excel-tables-features/compare
