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.
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 | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | ||||||||||||||||||
| 2 | Transaction ID | Tax Year | Month | Week | Date | In/Out | Description | Category | Gross Income | Tax Paid | Net Income | Debits | Balance | Tax Year Start | ||||
| 3 | 1 | 2021-2022 | April | 52 | 1/04/2021 | Starting Balance | 5000 | 1st April | ||||||||||
| 4 | 2 | 2021-2022 | April | 52 | 1/04/2021 | Income | 1000 | 6000 | ||||||||||
| 5 | 3 | 2021-2022 | April | 52 | 1/04/2021 | Outgoings | 500 | 5500 | Starting Balance | |||||||||
| 6 | 5000 | |||||||||||||||||
Sheet2 | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3:C5 | C3 | =IF(MONTH(F3)<4,YEAR(F3)-1 & "-" & YEAR(F3),YEAR(F3) & "-" & YEAR(F3)+1) |
| D3:D5 | D3 | =TEXT(F3, "MMMM") |
| E3:E5 | E3 | =INT((F3-WEEKDAY(F3,2)-DATE(YEAR(F3+276-WEEKDAY(F3,2))-1,4,7))/7)+2 |
| B4:B5 | B4 | =B3+1 |
| N3 | N3 | =Table6[Starting Balance] |
| N4:N5 | N4 | =SUM(N3+[@[Net Income]]-[@Debits]) |
