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

[Jamsandwich]

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 -

Batch	AH	CB	CH	CO
3936875	40	0	0	0
3936876	19	0	0	0
3936877	16	0	0	0
3936878	13	0	81	0
3936879	33	0	0	10
3936880	116	0	0	0
3936881	34	0	654	5
3936882	193	0	0	8
3936883	31	40	13	0

<tbody>
</tbody>

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 -

=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

=BatchNos

formula, but how can I make the table on the new workbook shrink and grow as required?