Creating a report from a table

[bullwinkle55423]

Is there a simple way to take a file that is formatted like this....

AcctNum AcctDescr Year Month Scenario Amount
10110 Food 2009 1 Actuals 1,000
10115 Travel 2009 1 Actuals 1,100
10110 Food 2009 2 Actuals 2,000
10115 Travel 2009 2 Actuals 2,100
10133 Gifts 2009 2 Actuals 2,200
10110 Food 2009 1 Budgets 1,500
10115 Travel 2009 1 Budgets 900
10133 Gifts 2009 1 Budgets 1,200
10110 Food 2009 2 Budgets 2,500
10115 Travel 2009 2 Budgets 1,900
10133 Gifts 2009 2 Budgets 2,200


And create a report that looks like this?

Summary
February 2009

Actuals Budgets
10110 Food 1,500 2,500
10115 Travel 900 1,900
10133 Gifts 1,200 2,200

Total 3,600 6,600

Take into consideration that additional data will be added each month and new accounts will be added each month. I'd like to be able to insert a row, add the new account number and then be able to copy the formulas from the row above it to get the new numbers.

I know that one option would be a pivot table, but it's not practical in this scenario.

Any other options?

I've included a sample worksheet if that helps.

Thanks.

[ConneXionLost]

Hi bullwinkle55423,

Here's a small modification to your sample worksheet that may give you part of the answer.

Cheers,

[teylyn]

Hi

change C20 to be a real date, not text. Format it with custom format "mmmm yyyy" so it looks like you have entered.

then, enter this formula in cell C23, copy down and right

=SUMPRODUCT(--($C$2:$C$12=YEAR($A$20)),--($D$2:$D$12=MONTH($A$20)),--($E$2:$E$12=C$22),--($B$2:$B$12=$B23),$F$2:$F$12)

[Richard Buttrey]

Hi,

Why is a Pivot Table not practical? It does exactly what you want and only takes a few seconds.

Rgds

[Mikeopolo]

My thoughts too, so for anyone wondering how a pivot table might work, here's an example, with a dynamic input range that will expand to include new data.

Page fields allow year/month to be selected from drop-downs.

Regards
Mike