Excel – Mysterious “Reference not valid”

Welcome to Superuser.

You are receiving this error because the tables are trying to expand to display the data which meets the pivot criteria. The tables have to grow to to do this.

What you are asking appears to be a question asked over several years on many forums. Unfortunately, there isn't a definitive answer. It seems to be that the pivot table function was not designed to play nice with other pivot tables on the same sheet.

If your pivot tables MUST be on the same sheet, I make the following suggestion; place them side-by-side. You will need to have an idea how many columns will be needed for each table as it expands. Then place a gap between the tables which can allow for the expansion of the table.

I think you were on the right track with Power Query. Generally, the steps are:

1. Load each table into Power Query (it's a pain, but I don't know of any bulk load option). You can set the default option to only load the connection, so you don't have duplicate sheets with the Power Query version of each table.

2. Open your first table in Power Query Editor. Choose Append Queries (not merge) from the Combine section of the Ribbon.

3. In the Append dialog box, select your second table and then OK. Repeat for each table you've loaded to Power Query. You're now adding the rows from each of your 60 tables into one "super table".

4. When all of your loaded tables have been added, load your Append Query to Excel.

5. Use the Append Query Table as a data source to create a Pivot Table. Select Art-No. and Description as your row labels (format as Tablular Layout, no subtotals) and Quantity as your values.