T
tomjoe
I am making an excelfile that I am trying to keep easy for the user to update.
Mainly I have a sheet1 “database†where I have made a userform for the user
to easily fill in text, values and dates into the sheet. The sheets range are
named for dynamic expansion as new rows are added.
Sheet1
A B C D E
1 X 10 01.04.2010 10 000
2 Y 15 01.04.2010 10 200
3 Z 30 01.04.2010 9 800
2 Y 40 15.04.2010 10 500
3 Z 50 01.05.2010 9 750
3 Z 20 01.05.2010 10 150
1 X 35 01.06.2010 10 200
This sheet is, with an BeforeSave procedure, automatically expanding a Pivot
table in sheet2 whenever there is new rows added in the named range in sheet1.
In sheet2 with the automatically expanding Pivot table (Table1) I also have
a Pivot table with the forecast (Table2).
Sheet2 - Table1
Sum of E
Year D A B C Total
2010 apr 1 X 10 10 000
2 Y 15 10 200
40 10 500
3 Z 30 9 800
mai 3 Z 20 10 150
50 9 750
june 1 X 35 10 200
-- Table2
Sum of Y
Year X Total
2010 apr 30 000
mai 15 000
jun 12 000
jul 20 000
aug 10 000
sep 8 000
okt 9 000
nov 10 000
des 12 000
This step is where I need some input:
I now want to make a list from the two Pivot tables in order to make a chart
(graphic overview). How can I make a list from the pivots in order to make a
dynamic chart:
List
Year Month Available Forecast Div acc
2010 apr 40 500 30 000 10 500
2010 mai 19 900 15 000 15 400
2010 jun 10 200 12 000 13 600
2010 jul 20 000 -6 400
2010 aug 10 000 -16 400
2010 sep 8 000 -24 400
2010 okt 9 000 -33 400
2010 nov 10 000 -43 400
2010 des 12 000 -55 400
Then the CHART.
Thank you for wise inputs...
Mainly I have a sheet1 “database†where I have made a userform for the user
to easily fill in text, values and dates into the sheet. The sheets range are
named for dynamic expansion as new rows are added.
Sheet1
A B C D E
1 X 10 01.04.2010 10 000
2 Y 15 01.04.2010 10 200
3 Z 30 01.04.2010 9 800
2 Y 40 15.04.2010 10 500
3 Z 50 01.05.2010 9 750
3 Z 20 01.05.2010 10 150
1 X 35 01.06.2010 10 200
This sheet is, with an BeforeSave procedure, automatically expanding a Pivot
table in sheet2 whenever there is new rows added in the named range in sheet1.
In sheet2 with the automatically expanding Pivot table (Table1) I also have
a Pivot table with the forecast (Table2).
Sheet2 - Table1
Sum of E
Year D A B C Total
2010 apr 1 X 10 10 000
2 Y 15 10 200
40 10 500
3 Z 30 9 800
mai 3 Z 20 10 150
50 9 750
june 1 X 35 10 200
-- Table2
Sum of Y
Year X Total
2010 apr 30 000
mai 15 000
jun 12 000
jul 20 000
aug 10 000
sep 8 000
okt 9 000
nov 10 000
des 12 000
This step is where I need some input:
I now want to make a list from the two Pivot tables in order to make a chart
(graphic overview). How can I make a list from the pivots in order to make a
dynamic chart:
List
Year Month Available Forecast Div acc
2010 apr 40 500 30 000 10 500
2010 mai 19 900 15 000 15 400
2010 jun 10 200 12 000 13 600
2010 jul 20 000 -6 400
2010 aug 10 000 -16 400
2010 sep 8 000 -24 400
2010 okt 9 000 -33 400
2010 nov 10 000 -43 400
2010 des 12 000 -55 400
Then the CHART.
Thank you for wise inputs...