Register
Log In
Unanswered
Home
September 26, 2016
Offline
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
July 16, 2010
Offline
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
Most Users Ever Online: 57
Currently Online: clark shao
15 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Md. Rahman
Lilian Vo
Jane Kumwenda
David Miller
Efe Onwochei
LynAnn Williams
Brandy Kriger
purna chandar
E K
velissa barr
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
