Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal/Consolidate/Pivot Table, Lookup ??? | Excel Discussion (Misc queries) | |||
Adding months to dates should account for 28-30-31 day months | Excel Worksheet Functions | |||
Need More Help on Dates to Months | Excel Worksheet Functions | |||
Months between two dates | Excel Programming | |||
Dates to months and calculating values for their months | Excel Programming |