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 -
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
formula, but how can I make the table on the new workbook shrink and grow as required?
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