Compare budget to actual

[Albert]

Hi Guys,

I have drawn a blank. Please help?

I have a spreadsheet which affectively are all items from my bank statement.
In another spreadsheet I have all the catagories from my bank statement
which is then filtered (sumif) between 2 dates.

I would like to then to compare my budget against the actual but I have my
budget setup in a cross tab table (categories in the rows and months in the
columns)

What formula could I use to pull through the corresponding budget to the
date range in the bank summary sheet?

Thanks
Albert

 

[smartin]

Hi Guys,

I have drawn a blank. Please help?

I have a spreadsheet which affectively are all items from my bank statement.
In another spreadsheet I have all the catagories from my bank statement
which is then filtered (sumif) between 2 dates.

I would like to then to compare my budget against the actual but I have my
budget setup in a cross tab table (categories in the rows and months in the
columns)

What formula could I use to pull through the corresponding budget to the
date range in the bank summary sheet?

Thanks
Albert

I can't see your layout but this is the general idea. Use INDEX/MATCH to
find the budget values based on keys in the actual table.

Suppose your budget looks like this in budget!A14

Category Jan Feb Mar
A 400 100 400
B 100 300 100
C 200 200 300

And suppose your bank info looks like this in actual!A14

Category Month Actual Budget
A Feb 100
B Feb 200
C Feb 300

You want to fill in the Budget column (D24).

In actual!D2 put

=INDEX(budget!$B$2:$D$4,MATCH(actual!$A2,budget!$A$2:$A$4,0),MATCH(actual!$B2,budget!$B$1:$D$1,0))

This can be filled down as needed.

Hope this gives you some ideas.