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.
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/
