Log In
Register
Home
November 13, 2020
Offline
Hi all,
Head scratching...
Each year you are allowed an allowance (the amount can vary)
You can use up to the full amount.
If you do not use the full amount whatever is left is an outstanding balance.
You can use this outstanding balance in future years for up to 3 years.
At the moment it's a very manual process.
Manually I can show remaining balance for 1 year.
What I can't figure out is how to 'automatically' use 3 years previous balances up to the allowance threshold
Sheet 1 is how it's currently done. Manually each year, checking the remaining balance off.
Sheet 2 is just an idea of how it could be presented (I think)
I think the biggest issue, maybe it's current layout.
I'm wanting to show if I have used previous years remaining balance has been used up
Each year another year will be added on, I want to keep the previous history.
I've tried offset - yet not working for me.
Trusted Members
February 13, 2021
Offline
No attachment, don't forget after selecting your file to click start upload and wait for the grey check mark.
November 13, 2020
Offline
Hi Jessica.
I really thought I had.
Manged to work it out, at least for the above query.
If wanting to sum the last 3 rows :
=SUM(OFFSET(E4,ROW()-ROW(E4),0,-3,1))
Headers, start in E4. Values start E5.
If you want to do the same but across columns:
=SUM(OFFSET(C15,0,COLUMN()-COLUMN(C15)-2,1,3))
'Header' labels in c15
Values in d15 and across.
Another query, for another post 🙂
The following users say thank you to Andrew Wilkie for this useful post:
Jessica Stewart
1 Guest(s)
