How can I combine the 'Budget' in categories and compare against actual transactions in a pivot table?

Google Sheets
1

Hi there,

I really enjoy looking at transactions in a pivot table, I have added additional categories that make it easier for my to dissect my transactions. However, I would like to compare the actuals against the budgeted values in the same view, but the budgeted values are in the ‘Categories’ tab, and not in a flat format.

I am trying to effectively transpose the budget values in the categories tab into a flat version to compare against actuals while maintaining the link to the categories value cell, and not actually adding it to the transactions list.

2

Another challenge is I want the ‘Budget’ value in negatives (currently in positives in the categories tab), so I can offset income against expenses in the pivot

3

I’d consider just creating a new sheet and writing some simple formulas. The first column could pull from the Categories sheet 1:1. The second column could pull the relevant budget month from the Categories sheet 1:1. The third column could do a SUMIFS with the category name and the date range for each transaction.

1 Like
4

Hey @randy - sorry for the delay in following this up, my Tiller was down for a few weeks.

Could you please explain a bit more about how I would set this up? E.g. what formulas would I use in the columns, is there a way I can flatten the categories/budget sheet and also link it?

5

I figured it out, thank you so much!

1 Like