If you can kindly look at the attached file, you will see that under cell C73:X78, I have a summary list for every Location. What I am trying to do is for the balance at the end of each month to auto populate in cells M73:X78. The balance would be the Opening Written Down Value under column K less the expenses for each month as given on the upper table. Or if in case an Asset was acquired later than the opening date, then balance would be the cost less the expenses for each month for that asset and location as given on the table.
The problem I have at the moment is for Location 1 and Location 5, the formula I currently have, is not calculating correctly. This is since because for Location 1 and 5 there are assets that came in after the Opening Balance as at 30 June 2018.
So you'd notice under October, the value I have for Location 1 is off by $1,480.91 which is the value of asset acquired in October 2018. By November, location 1 is off by $2,525.46 which is the value of asset acquired in October plus the value acquired in that same month.
As for Location 5, the issue I have is somewhat the same, however, in this, instead of the cost, it is the expense from previous months that I couldn't capture.(This is why I had been thinking that I needed an offset function, so that I can incorporate that in my formula and the value from previous month would carry over).
I may be wrong to think that offset would be useful to this but I had been trying to find in google and youtube ways where I can do this. And I have combined a lot of other formulas already but couldn't come up with the correct combination.
I hope this is something any one here can help me with. I'd be so much grateful. Thanks a ton!
Bookmarks