Consolidate, Lookup and Sum from dates to months


E

Excelfan

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
 
Ad

Advertisements

E

Excelfan

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top