Compiling Data from SharePoint Export in Excel

[HopelessGent]

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!

[#2 · Accepted solution]

Hi @HopelessGent 

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

[#3]

@L z.   Thank you so much!  This worked beautifully.