Calculating the most recent value

[mattP]

Hi,

I have a spreadsheet where I capture proposal information, including customer name, status, who prepared the proposal, date prepared and the value.

I also then have a number of columns where I can log any amendments to the proposal cost, where by I log the date and the new cost.

How could I get a "Current Value" column to show the most recent amendmend, if any, for that row ?

So basically some proposals will have no amendments, some will have 2 - 4 ?


Hope this is not too vague

Many thanks

MattP

 

[PaulJR]

I believe you need to use MAX and LOOKUP. If for example you had the date in column A, and the cost in column B (assuming row 1 contains titles and you have a maximum of 100 rows), then give this a try:

=LOOKUP(MAX(A2:A100),A2:B100)

 

[Brianwarnock]

It sounds like you want to duplicate the last value on a row into a specific column.
This should be simple as the value is always the last data entered therefore assume 5 cols for the original and possible 8 for the amendments therefor in col O say
you enter =latest(O2) in row2 and have the following in a module, a crude bit of code but I think it will work
Brian

Function latest(parameter As Range)
a = parameter.Row
For c = 13 To 1 Step -1
If Cells(a, c).Value > 0 Then
latest = Cells(a, c).Value
Exit Function
End If
Next c
End Function

 

[mattP]

Hi Brian,

Many thanks for that, it worked, I just had to calculate the last cell to work back from and it totals up for me nicely.

Thanks for the quick response

Mattp

 

[Brianwarnock]

Are you totalling the original plus all of the amendments?
If so you just need =sum(..)
eg
=SUM(E2,G2,I2,K2,M2)
Unlike Access empty Cells do not stop the calculation.

Brian