Forum Discussion
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 ...
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!
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
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