2

I am inputting dated amounts into a single column. The newest data will then be divided by a constant that I have at the top of the page. I would like the formula of "newest amount" divided by "constant" to automatically update when I input new data.

Here is a simple setup of what I mean:

example worksheet

As I have it now the formula would read =(B5/$D$2)

Then when I add data to B6, I must manually change to formula to reflect that.

Is it possible when I add data to B6, B7, and so on that the formula can adjust automatically?

Edit for clarity: I would want to formula to adjust based on cell position alone, not related to a modification time/date.

Skooba - Stands Against AI
  • 323

2 Answers2

2

You can use this:

=INDEX(B:B,MATCH(1E+99,B:B))/$D$2

The INDEX/MATCH will return the value in the last cell in column B that has a number in it. So as numbers are added to column B the reference will change automatically to the last number.

enter image description here

Scott Craner
  • 23,868
2

If you just want it (as per your example) to be based upon the last value in B column you can use

=LOOKUP(2,1/(NOT(ISBLANK(B:B))),B:B)

So your formula would be

=(LOOKUP(2,1/(NOT(ISBLANK(B:B))),B:B))/$D$2

This has many issues, if you made a change mid way through your B column then you'd have still do it manually!

If you want it to fire based upon the most recent row you've edited, then you'd need to use VBa's Worksheet_Change event

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Column = 2) Then
        Range("E2").Value = Range("D2").Value / Target.Value
    End If    
End Sub

How do I add VBA in MS Office?

Please remember, there is no undo with VBa, so take a back up first and make sure you save the file as a Macro enabled file.

Community
  • 1
Dave
  • 25,513