If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Compare budget to actual
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 |
#2
|
|||
|
|||
Compare budget to actual
Albert wrote:
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. |
Thread Tools | |
Display Modes | |
|
|