Table Created From Values in Pivot Table With Dynamic Number of Rows

Jamsandwich

New Member
Joined
Sep 25, 2014
Messages
44
Hey guys,

I'm currently trying to figure out a problem and I'm at my wit's end. Hopefully somebody here can help.

On one of my worksheets I have a Pivot Table filled with values from a dataset (from SQL query). It looks a little like this -

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Batch[/TD]
[TD]AH[/TD]
[TD]CB[/TD]
[TD]CH[/TD]
[TD]CO[/TD]
[/TR]
[TR]
[TD]3936875[/TD]
[TD]40[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3936876[/TD]
[TD]19[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3936877[/TD]
[TD]16[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3936878[/TD]
[TD]13[/TD]
[TD]0[/TD]
[TD]81[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3936879[/TD]
[TD]33[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]3936880[/TD]
[TD]116[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]3936881[/TD]
[TD]34[/TD]
[TD]0[/TD]
[TD]654[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3936882[/TD]
[TD]193[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3936883[/TD]
[TD]31[/TD]
[TD]40[/TD]
[TD]13[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

As a pivot table, Excel automatically set it up for the headers to be on row 4 with data starting on row 5.

I now want to copy that data onto a new worksheet with lots of additional calculation columns that I'll use to generate dynamic charts. The below formula works for my table where I am sorting the data by date, as there is always the same amount of rows -

Code:
=RejectByDay!A6

I started by using a similar formula to copy over the above table. My problem is that, day to day, the pivot table won't always have the same amount of rows. This means that the table may be shorter than required, or worse, longer (this screws my formula columns).

I tried turning the batch column into a named range and using a simple
Code:
=BatchNos
formula, but how can I make the table on the new workbook shrink and grow as required?
 

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top