0

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.

Improve this question
  • Consider =SUM(B4:B9)-B10, as values are entered in B5, etc. they would affect the value in B11 – Gary's Student Jan 29 '17 at 21:29
  • do you want the formula to change from B4-B10 to B5-B10 or do you want B4+B5-B10 like @Gary'sStudent student suggests? – Scott Craner Jan 29 '17 at 22:12
2

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.

enter image description here

Improve this answer
  • would you have a way to do this if the last value entered was not necessarily the in the last cell in the column? (e.g i enter values in C4, C6, then C5, so the last cell entered, chronologically, is the one used in C11)? – eli-k Jan 30 '17 at 5:36
  • 1
    @eli-k not with a formula. In that case you'd need a Worksheet_Change event macro that would fire when a cell between B1 and B9 is changed and would write that cell address into the formula, or do the calculation altogether. – teylyn Jan 30 '17 at 7:06
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.