Jan 27 2022 07:58 AM
All,
I appreciate anyone's thoughts on this problem I am having:
I have a SharePoint list that exceeds 5K items. I have built a number of filtered views to ensure that no 1 view exceeds 5K (the ceiling for a view in our environment is 5K).
There is one spreadsheet that I use to export all of the data from the various views to separate worksheets. I would like to automate a function that would compile the data from the various spreadsheets into 1 collective sheet.
I have formulas that can pull the data from the exported sheets but ideally I would like to place the formulas in successive rows, so that no additional sorting would be needed. When I place the formulas in successive rows I get the spill error.
I am using a PC with Excel 2008. I have some experience with excel formulas but none with VBA.
Any recommendations are appreciated. Thank you!
Jan 27 2022 09:36 PM
Solution
Assuming all your lists have the same structure Get & Tranform aka Power Query is your friend
1/ For each list/table:
- Go to Data (tab) > From Table/Range (this opens the Power Query editor)
- Click on the little arrow down next to Close & Load > Close & Load To... > Check Only Create Connection
2/ Once all your connections are established Append all queries as a single table that you'll load on a new worksheet
Jan 31 2022 10:39 AM
Jan 27 2022 09:36 PM
Solution
Assuming all your lists have the same structure Get & Tranform aka Power Query is your friend
1/ For each list/table:
- Go to Data (tab) > From Table/Range (this opens the Power Query editor)
- Click on the little arrow down next to Close & Load > Close & Load To... > Check Only Create Connection
2/ Once all your connections are established Append all queries as a single table that you'll load on a new worksheet