Dynamic Subtraction Cells Get Changes

sg2209

Board Regular
Joined
Oct 27, 2017
Messages
117
Office Version
  1. 2016
Good Evening ,
I have searched google and looked sveral you tube videos unfortunatley could not find what i am looking for .

i have the below snapshot in Column B has Formula and Result are just below each cell when i have to add the new data i need to insert a row everytime .

Cell B6 has the Formula = B5 - F5 and the result is in B6
Cell B7 has the Formula = B6 - F6 and the result is in B7.

I need Last B10 Should be auto populate in Cell B2, Cell B10 keeps on changing, B11 , B12 etc and Value in B2 can auto pick from last cell from column B

please review if

1619607139987.png
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
I am not sure I am correctly understanding the requirements.
To me it sounds like you just want the last row returned in cell B2 (eventhough B2 is not part of the image).

If that is the case I suggest turning the data range into an Excel Table.
To do this, click anywhere in the table and go Insert > Table OR press Ctrl + T.
Then under Table Design > in the white box on the far left give it a table name.
If you put in the same table name as I have you should be able to copy the B2 formula straight in.

In the below my table name is tblBal

20210505 Get Last Row.xlsx
ABCDEF
1
2Last row figure25
3
4Deposit DateStarting BalanceBatch #Batch Amount $$Net Deposit AmountDebit of ERA
550
6502
7485
84310
9333
10305
1125
Sheet1
Cell Formulas
RangeFormula
B2B2=INDEX(tblBal[Starting Balance],COUNTA(tblBal[Starting Balance]),0)
B6:B11B6=B5-F5
 
Upvote 0
Th
I am not sure I am correctly understanding the requirements.
To me it sounds like you just want the last row returned in cell B2 (eventhough B2 is not part of the image).

If that is the case I suggest turning the data range into an Excel Table.
To do this, click anywhere in the table and go Insert > Table OR press Ctrl + T.
Then under Table Design > in the white box on the far left give it a table name.
If you put in the same table name as I have you should be able to copy the B2 formula straight in.

In the below my table name is tblBal

20210505 Get Last Row.xlsx
ABCDEF
1
2Last row figure25
3
4Deposit DateStarting BalanceBatch #Batch Amount $$Net Deposit AmountDebit of ERA
550
6502
7485
84310
9333
10305
1125
Sheet1
Cell Formulas
RangeFormula
B2B2=INDEX(tblBal[Starting Balance],COUNTA(tblBal[Starting Balance]),0)
B6:B11B6=B5-F5
THank You


Thank You @Alex Can not we do it wihtout Table ? through any function .
 
Upvote 0
Can not we do it wihtout Table ? through any function .
This will do it without a table.

Excel Formula:
=LOOKUP(2,1/(B:B<>""),B:B)
OR if you have Excel 365
Excel Formula:
=XLOOKUP(TRUE,B:B<>"",B:B,"",0,-1)

There are a lot of advantages in using a table. In your case one of them is that it will automatically populate the formulas down as you add more rows.
It also means that lookups don't need to look at Entire Columns but can just use the table range which automatically expands as you add rows.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

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?

Disable AdBlock

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
Back
Top