How do I create a cell to reflect the last entered value in a column?

I love spreadsheets but sometimes I think they are winning the battle when I try and extract a function from them. Help me control my spreadsheet.

Here is a sample of what I am trying to do.

[summary] I am trying to get a cell to reflect the a relationship between two numbers in a column. So in other words, I am needing a dynamic cell to reflect when a new number is added to the column.

Currently B11 =B4-B10 (B10 is "goal")

If I enter a value in B5 then I would like the value in B11 to change to reflect that. Then later, when I add a value to B6 the value in B11 then reflect that cell. So on and so forth.

I wish I could actually post a short version of that here but I don't see how.

Solution 1:

this formula will use the last cell with a number in the calculation in B11:

=INDEX($B$1:$B$9,MATCH(99^99,$B$1:$B$9,1))-B10

In the screenshot, this will be B4. If you enter anything in B5, B11 wil use B5. The data from B1 to B9 can have empty cells. It is the last cell with a number that gets used for the calculation.

Solution 2:

You can create a cell that contain the last content by using the following formula:
=CELL("contents")
write the above formula in A1, it will reflect the contents of whatever cell you modify. In B11 write
=value(A1)-B10
B10 is where your Goal is.