I've taken on a small project for my finances, I've created a tabel with incomes, Expense and totals. (Example table with imaginary numbers - Seen Below)
The profit column is calculated using this formula & is the Sum of the profits minus the Sum of Expense
=SUM(Table1[[#This Row],[Wage]:[Others]])-SUM(Table1[[#This Row],[Bills]:[Savings]])
Then Balance is calculated using the formula & is this weeks profits plus last weeks Balance
=Table1[[#This Row],[Profits ]]+L8
However if i go to rearrange the table to have the highest profits at the top all things go mad in the balance column.
As you can see this has caused some issues in the Balance column. So my question is such;
Is it possible to create a formula that allows Excel to remeber the row that was used in the calculation or not? So that the balance column numbers stay the same relative to the rearrenged column? i.e. After the table has been re-arrenged the Balance Cell next to the Profit cell that contains (£175.99) says (£4,015.93) rather than (£357.71)
Having done some reasurch I'm pretty sure that the issues is caused by the [# This Row] I was trying to find a way to say something like [# This Cell] <- Pseudo Code to tell Excell to remeber this Cell's address.
I hope this all makes sense.
Thank you for any help that you may be able to provide!
Having spent some more time on this problem, I tried to use the look-up method. I have added a new Column at the start of the Table - with the number 1,2....
I then use the formula =LOOKUP(4,[Look-Up ],[[Profits ]]) + LOOKUP(3,[Look-Up ],[Balance])
This gives me the correct balance in the 4th row but still dosen't work when the table is re-arrenged
Having spent some more time with learning about using "LookUp" formulas and the such, I've came up with the idea that another issue with this will be in calculating the profits column.
Using the "Look-Up" Column, I now have my profits column calculated using the equation =LOOKUP(3,[Look-Up ],[Wage] + [[Shares ]] + [Others] - [Bills] -[[Food ]]-[[Hobbies ]]-[Other]-[Savings])
This works great until, like the rest of the issue, the table is rearange when it falls through. This then took me onto trying to concentrate the formula in the eqn bar like you would in VBA, trying to create a formula something like this
=Cell("C"&Text(6+LookUp(1,[Look-Up],[Look-Up]),"0")
the idea of this eqn is to diplay the value of the cell in column C with the Row found in the lookup table. This dosnt work yet, there is an error in the formula somewhwere.
