Dynamic Creation/update of Pivot table data

[forbzinator]

Hi guys

Excel 2007, able to use VBA within this document.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1			Logged Time
2	TM Name	Chordiant ID	11/11/2011	12/11/2011	13/11/2011	14/11/2011	15/11/2011	16/11/2011	17/11/2011	Total
3	Name 1	id1	1	2	3	4	5	6	7	8
4	Name 2	id2	1	2	3	4	5	6	7	8
5	Name 1	id3	1	2	3	4	5	6	7	8
6	Name 2	id4	1	2	3	4	5	6	7	8
7	Name 1	id5	1	2	3	4	5	6	7	8
8	Name 6	id6	1	2	3	4	5	6	7	8
Sheet1

Excel Workbook
	K	L	M	N	O	P	Q	R
1	Not Ready
2	11/11/2011	12/11/2011	13/11/2011	14/11/2011	15/11/2011	16/11/2011	17/11/2011	Total
3	9	10	11	12	13	14	15	16
4	9	10	11	12	13	14	15	16
5	9	10	11	12	13	14	15	16
6	9	10	11	12	13	14	15	16
7	9	10	11	12	13	14	15	16
8	9	10	11	12	13	14	15	16
...

Excel 2007



I am struggling to work out how best to combine this weekly report into 1 raw data dump for using with a pivot. The main problem being there are 2 headings for the columns.

First is the stat name (which is only is the first cell for the range then the rest are blank) then the next row shows 7 days and a total. The above example is only for 2 stats to give an idea of how the series goes in total there are 16 more of these following the same pattern.

What I woud like is to be able to create a pivot so I can view the full date range on the above report and any subsequent ones that are generated.

Any help would be massively appreciated.


Thanks

 

[Andrew Poulsom]

Isn't it in C1 in your original data?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1			Logged Time[/B]
2	TM Name	Chordiant ID	11/11/2011
Sheet1

 

[forbzinator]

Ah! It was in B1 when I exported the sample to HTML my excel had crashed so I copied the info back direct from the post and it appears to have not aligned it correctly going back in.

This now works for the stats heading but still doesnt list the total from "date"

Thanks again for this much appriecated.

 

[forbzinator]

It appears to be something to do with the fact that the 8th entry is "Total" and not a numeric value if I change it to a date then it carries it across.

Any ideas?

 

[Andrew Poulsom]

I deliberately omitted the Total column as I don't believe you need it for your pivot table.

 

[forbzinator]

I deliberately omitted the Total column as I don't believe you need it for your pivot table.

Andrew you are a gent! Thankyou for your assistance and for welcoming me to the community.