Consolidate, Lookup and Sum from dates to months
 

How can I automatically consolidate two new lists from three tables and also
dynamically expand the lists when new items are added. How can I convert the
dates directly from days in table 3 to months in Result A and B?

Table 1
Item Month Forecast
A apr.10 30 000
A mai.10 15 000
A jun.10 12 000
A jul.10 20 000
A aug.10 10 000
A sep.10 8 000
A okt.10 9 000
A nov.10 10 000
A des.10 12 000


Table 2
Item Month Forecast
B apr.10 15 000
B mai.10 5 000
B jun.10 6 000
B jul.10 8 500
B aug.10 6 000
B sep.10 3 000
B okt.10 4 500
B nov.10 4 000
B des.10 6 000


Table 3
Item Date Available
A 01.04.2010 10 000
A 01.04.2010 10 200
B 01.04.2010 9 800
A 15.04.2010 10 500
B 01.05.2010 9 750
B 01.05.2010 10 150
A 01.06.2010 10 200
B 01.07.2010 9 850



Result A
Item Month Forecast Available Acc Div
A apr.10 30 000 30 700 700
A mai.10 15 000 -14 300
A jun.10 12 000 10 200 -16 100
A jul.10 20 000 -36 100
A aug.10 10 000
A sep.10 8 000
A okt.10 9 000
A nov.10 10 000
A des.10 12 000


Result B
Item Month Forecast Available Acc Div
B apr.10 15 000 9800 -5 200
B mai.10 5 000 19900 9 700
B jun.10 6 000 3 700
B jul.10 8 500 9850 5 050
B aug.10 6 000 -6 000
B sep.10 3 000
B okt.10 4 500
B nov.10 4 000
B des.10 6 000

Consolidate, Lookup and Sum from dates to months
 

Try a PivotTable based off dynamic named ranges.
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm



--
Regards
Dave Hawley
www.ozgrid.com
"Excelfan" wrote in message
...
How can I automatically consolidate two new lists from three tables and
also
dynamically expand the lists when new items are added. How can I convert
the
dates directly from days in table 3 to months in Result A and B?

Table 1
Item Month Forecast
A apr.10 30 000
A mai.10 15 000
A jun.10 12 000
A jul.10 20 000
A aug.10 10 000
A sep.10 8 000
A okt.10 9 000
A nov.10 10 000
A des.10 12 000


Table 2
Item Month Forecast
B apr.10 15 000
B mai.10 5 000
B jun.10 6 000
B jul.10 8 500
B aug.10 6 000
B sep.10 3 000
B okt.10 4 500
B nov.10 4 000
B des.10 6 000


Table 3
Item Date Available
A 01.04.2010 10 000
A 01.04.2010 10 200
B 01.04.2010 9 800
A 15.04.2010 10 500
B 01.05.2010 9 750
B 01.05.2010 10 150
A 01.06.2010 10 200
B 01.07.2010 9 850



Result A
Item Month Forecast Available Acc Div
A apr.10 30 000 30 700 700
A mai.10 15 000 -14 300
A jun.10 12 000 10 200 -16 100
A jul.10 20 000 -36 100
A aug.10 10 000
A sep.10 8 000
A okt.10 9 000
A nov.10 10 000
A des.10 12 000


Result B
Item Month Forecast Available Acc Div
B apr.10 15 000 9800 -5 200
B mai.10 5 000 19900 9 700
B jun.10 6 000 3 700
B jul.10 8 500 9850 5 050
B aug.10 6 000 -6 000
B sep.10 3 000
B okt.10 4 500
B nov.10 4 000
B des.10 6 000

Consolidate, Lookup and Sum from dates to months
 

OK, I know about the multiple consolidation possibility and the named range
trick to get a pivot table updated, but I cannot figure out how to
incorporate table 3 with table 1 and 2.




"ozgrid.com" wrote:

Try a PivotTable based off dynamic named ranges.
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.ozgrid.com/Excel/DynamicRanges.htm



--
Regards
Dave Hawley
www.ozgrid.com
"Excelfan" wrote in message
...
How can I automatically consolidate two new lists from three tables and
also
dynamically expand the lists when new items are added. How can I convert
the
dates directly from days in table 3 to months in Result A and B?

Table 1
Item Month Forecast
A apr.10 30 000
A mai.10 15 000
A jun.10 12 000
A jul.10 20 000
A aug.10 10 000
A sep.10 8 000
A okt.10 9 000
A nov.10 10 000
A des.10 12 000


Table 2
Item Month Forecast
B apr.10 15 000
B mai.10 5 000
B jun.10 6 000
B jul.10 8 500
B aug.10 6 000
B sep.10 3 000
B okt.10 4 500
B nov.10 4 000
B des.10 6 000


Table 3
Item Date Available
A 01.04.2010 10 000
A 01.04.2010 10 200
B 01.04.2010 9 800
A 15.04.2010 10 500
B 01.05.2010 9 750
B 01.05.2010 10 150
A 01.06.2010 10 200
B 01.07.2010 9 850



Result A
Item Month Forecast Available Acc Div
A apr.10 30 000 30 700 700
A mai.10 15 000 -14 300
A jun.10 12 000 10 200 -16 100
A jul.10 20 000 -36 100
A aug.10 10 000
A sep.10 8 000
A okt.10 9 000
A nov.10 10 000
A des.10 12 000


Result B
Item Month Forecast Available Acc Div
B apr.10 15 000 9800 -5 200
B mai.10 5 000 19900 9 700
B jun.10 6 000 3 700
B jul.10 8 500 9850 5 050
B aug.10 6 000 -6 000
B sep.10 3 000
B okt.10 4 500
B nov.10 4 000
B des.10 6 000

Consolidate, Lookup and Sum from dates to months
 

Excel 2007 PivotTable, Tables
Consolidate Tables with macro.
Using real dates, not text.
http://c0718892.cdn.cloudfiles.racks.../04_11_10.xlsm