CODEWITHSUNDEEP
excelexcel-formulapowerquery
dummy105234234
dummy105234234

Reputation: 41

Replicating a pivot table in table format until the last non blank row

I'd like to recreate a pivot table but in a table format. Currently, the formulas in these table columns reference the Pivot table cell numbers expanded all the way down (to catch any new rows amongst a pivot refresh). Ideally whenever the pivot table updates, the table automatically updates as well (includes additional rows). Is there a way to format the table so that it is always the same size as the Pivot table, eliminating all these empty rows (21-beyond)? Thanks.

enter image description here

Upvotes: 1

Views: 58

Answers (1)

davidebacci
davidebacci

Reputation: 30174

This is not a simple topic that can easily be answered, especially without a proper sample data file. You can achieve what you want but you'll need to do some reading. First of all, have a read of the 3 ways to reference data from a pivot table here: https://exceloffthegrid.com/getpivotdata-vs-cube-functions/

You've already tried the first and found the drawbacks of it.

Secondly, once you're familiar with cube functions, have a read of Chris Webb's blog here where he uses spilled arrays to access the data model.

https://blog.crossjoin.co.uk/2019/11/10/excel-dynamic-arrays-and-the-cubeset-function/

Upvotes: 1

Related Questions