# Auto Updating Running Balance/Transaction ID & Tax Week Query

#### ChrisM89

##### New Member
Hello everyone, I've almost finished my budget dashboard spreadsheet, but I have a few niggly things I would like to tie up. Hopefully someone can help!

Please see table below, it's an example of the tables and formulas I am using.

Problem 1 - Is there a way to automatically update the running balance (ie. net income - debits) when a new transaction row is entered?
Problem 2 - Is there a way to automatically update the transaction ID in a similar way?

Both these issues can be resolved by dragging down from previous row but I'd rather not if it can be avoided.

Problem 3 - I am trying to start my tax week (weeks run 1-52) on 1st April every year. From example below it is reading 1st April as week 52 which is incorrect. I found this formula online from another user and whilst it is very good, it's not perfect.

Problem 4 - Is there a way I can automatically set the start of the tax year to the value entered in P3? This would be helpful if I was to send it friends in other countries with different tax dates.

Apologies in advance for the amount of questions. If someone can find a solution to all of the above I will be a happy man as I will be done with my spreadsheet, and my wife can stop nagging at me ?

Thank you.

Budget Dashboard.xlsx
ABCDEFGHIJKLMNOP
1
2Transaction IDTax YearMonthWeekDateIn/OutDescriptionCategoryGross IncomeTax PaidNet IncomeDebitsBalanceTax Year Start
312021-2022April521/04/2021Starting Balance50001st April
422021-2022April521/04/2021Income10006000
532021-2022April521/04/2021Outgoings5005500Starting Balance
65000
Sheet2
Cell Formulas
RangeFormula
C3:C5C3=IF(MONTH(F3)<4,YEAR(F3)-1 & "-" & YEAR(F3),YEAR(F3) & "-" & YEAR(F3)+1)
D3:D5D3=TEXT(F3, "MMMM")
E3:E5E3=INT((F3-WEEKDAY(F3,2)-DATE(YEAR(F3+276-WEEKDAY(F3,2))-1,4,7))/7)+2
B4:B5B4=B3+1
N3N3=Table6[Starting Balance]
N4:N5N4=SUM(N3+[@[Net Income]]-[@Debits])

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

Replies
4
Views
286
Replies
1
Views
959
Replies
3
Views
1K
Replies
5
Views
2K
Replies
3
Views
563

1,203,455
Messages
6,055,541
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back