Forum Discussion

Copper Contributor

Oct 24, 2020

Automatically insert rows on worksheet linked to Pivot Table

I'm using Excel in Office 365. I have a worksheet linked to a Pivot Table in the same workbook. The worksheet is linked via cell references on the pivot table table. Here's the problem: Whenever new ...

MVP

Oct 25, 2020

If you have Office 365 then it is possible to use Dynamic Arrays to automatically "Spill Down" the formulas as new rows appear in the Pivot Table.

Another option to consider is to use Power Query to pull the data into the new sheet. This would require clicking the refresh button to pull in the new rows.

Do you have Excel 365 (test by checking if you have the =SEQUENCE function) and have you used Power Query before?

Gregger
Copper Contributor
Oct 25, 2020
Hi Wyn Hopkins

I just tried to create a dynamic array on my worksheet that links to my pivot table, but i got a #REF! error. I just entered the cell reference from the pivot and put a # after it, like 'Pivot!"B6#. But I haven't created a dynamic array before, so maybe i did it wrong?

I'm vaguely familiar with power query, but don't know how to use it to build off my pivot table. But i wouldn't mind having to simply click refresh to update the linked table.

Lastly, i do have excel 365.

Thanks for the tips!
- Wyn Hopkins
  MVP
  Oct 30, 2020
  Hi,
  
  The # reference is used to reference an existing dynamic array rather than a Pivot Table.
  The concept can be that you reference a range with many more rows than the Pivot and then use a FILTER function to remove the empty rows dynamically...
  
  BUT...
  
  Can you clarify if you are manually adding any data to the new rows that get auto-added to the linked worksheet. I'm thinking there may be a risk that a new item gets added in a different row of the Pivot table and your inputs (if any) on the 2nd sheet would end up mis-aligned.
  
  If this isn't a risk then I can mock up a quick Power Query demo for you

Resources