98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Showing 'latest' months data in a summary | Excel forum
Showing 'latest' months data in a summary | Excel forum
Resolved · Urgent Priority · Version 2000
Sheila has attended:
Excel Advanced course
Showing 'latest' months data in a summary
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
RE: showing 'latest' months data in a summary
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
RE: showing 'latest' months data in a summary
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
RE: showing 'latest' months data in a summary
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
Training information:
See also:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:3D formulas find and replaceAfter you know all the components of a 3D reference, you can change them to suit by using a localised Find and replace crt+f, if need be. |