Newest Data...

[Shhhh]

Hello all,

Cell B4 has the formula ($B$2*B5)

B5 to B455 are going to be filled with data usually daily but sometimes more
than once a day.

How can I make the formula in B4 say multiply B2 by whichever cell is the
last one with data... and as i add data in the next cell, the formula
calculates based on new cell??


I hope I asked this question clearly.
Thank you,
Shhhh

 

[Guest]

Shhhh

Try this
=INDIRECT(ADDRESS(MATCH(MAX($B$5:$B$6553),$B$5:$B$65536)+4,COLUMN()))*B2

Regards
Peter

 

[Guest]

You already posted the correct formula. In B4 enter:

=$B$2*B5

As you add data to the column, always add it at the top, pushing the older
data down. That way the newest data will always be in B5

 

[Guest]

Or maybe I misunderstood. Do you need a Worksheet change event?

I gave the Range B5 to B455 the name InputRange (Insert, Name, Define)
specify name and range

B2 I name Rate

Then right_click the Sheet Name tab and select View Code copy the following
code into the worksheet code sheet.

Sub worksheet_Change(ByVal target As Excel.Range)
Dim VRange As Range, cell As Range
Set VRange = Range("InputRange"): Set r = Range("Rate")
For Each cell In target
If Union(cell, VRange).Address = VRange.Address Then
Range("B4") = WorksheetFunction.Round(cell * r, 2)
Exit Sub
End If
Next cell
End Sub

=========

Regards
Peter