Hi,

I produce a report that shows data per month Jan - Dec then a final column as a summary.

In the final column for some criteria I need a total year to date so use the sum function. For others I need to pull through the latest entry.

What formula should I put in the final column to pull through the latest month's information rather than total the months so far?

I tried using IF statements and ISBLANK arguments, even adding hidden columns to allow the number of arguments but cannot so far make it work.

Your help is very much appreciated.

Thanks

Sheila

Hi Sheila

To assist with your requirement will most likely involve some didicated time with a relevant trainer having access to your spreadsheet.

Naturally this will be consultative work and therefore billable.
If you wish to discuss this in more detail please do let us know.

Regards

Jacob

Hi Sheila

Thank you for your question

I have created a custom function in VBA which solves your problem. To install it:

1. Press ALT_F11 to open VBE
2. Create a new module by clicking on the second betton from the left on the toolbar and choose "module".
3. Then copy and paste the following code into the code window (Large window on right hand side)

Function LastValue(VarRange As Range) As Single

Dim i As Integer
Dim intTemp As Single


For i = 1 To VarRange.Columns.Count

If VarRange.Cells(1, i).Value = 0 Then

LastValue = intTemp
Exit Function


Else

intTemp = VarRange.Cells(1, i).Value
LastValue = intTemp

End If

Next i



End Function

This creates a function called LastValue; to use it

1. Select the cell where you want the value to go
2. Click on the function wizard and choose the function from the user defined category
3. You will need to select the range of cells to be tested, (all columns from jan to dec)
4. Click OK

Hope this solves your problem. Let me know if you have any further problems

Regards

Stephen

Stephen,

This is brilliant! I had been wracking my brain for weeks to find a way to do this and everything I tried worked for a month but not for more than that.

One thing I have found is that I need to put 0.000001 formatted to 2 dec places as the default entry in my spreadsheet as otherwise, if the report delivers a 0 it doesn't show as Latest, nor does the next entry as the VBA looks for the first entry after a zero.

I know I know, I want the moon on a stick!

I'm very grateful for your help and it has also made me more eager to learn some VBA.

Cheers

Sheila