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 » How to Add automated summary for a report based on certain metri
How to Add automated summary for a report based on certain metri
Resolved · High Priority · Version 2016
Stefanie has attended:
Excel Advanced course
How to Add automated summary for a report based on certain metri
I'm creating a dashboard and recall my previous manager showing me that there is a way/ formular that you could write so you it pulls in metrics from the report dynamically and he used this as an automated summary for the report. Can you please send me some notes on how to do this or an example sheet?
Thanks!
RE: How to Add automated summary for a report based on certain m
Hi Stefanie,
Thank you for the forum question.
If I understand you right you will need to use the Table tool.
I have attached one of our handbooks and if you read Unit 5, you will find, what you need.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
RE: How to Add automated summary for a report based on certain m
Hi Jen,
No, i'm not referring to a table.
What i'm asking is how to write a dynamic commentary based on the numbers from say a pivot table, a slicer and so on.
It should be formulars to put words together in one cell that can pull in the numbers from the pivot table / slicer so that when someone selects from a certain page, the commentary changes.
Cheers,
Stef
RE: How to Add automated summary for a report based on certain m
Hi Stefanie,
Please find the attached workbook and let me know if it is something like this you need.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
RE: How to Add automated summary for a report based on certain m
Hi Jen,
That's correct. Do you know whether it's possible to insert month based on the slicer? or would i need to add another column in my pivot table to show month so it could auto populate in the Summary cell?
Cheers,
Stef
RE: How to Add automated summary for a report based on certain m
Hi Stefanie,
It can be done but it is complicated.
You can write VBA code (see below), or you need to create the PivotTable in PowerPivot and the PivotTable must be based on a data cube. If it is created from a multi dimension data cube you can display the slicer information using the CubeRankedMember Function.
Public Function GetSelectedSlicerItems(SlicerName As String) As String
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim lCt As Long
On Error Resume Next
Application.Volatile
Set oSc = ThisWorkbook.SlicerCaches(SlicerName)
If Not oSc Is Nothing Then
For Each oSi In oSc.SlicerItems
If oSi.Selected Then
GetSelectedSlicerItems = GetSelectedSlicerItems & oSi.Name & ", "
lCt = lCt + 1
ElseIf oSi.HasData = False Then
lCt = lCt + 1
End If
Next
If Len(GetSelectedSlicerItems) > 0 Then
If lCt = oSc.SlicerItems.Count Then
GetSelectedSlicerItems = "All Items"
Else
GetSelectedSlicerItems = Left(GetSelectedSlicerItems, Len(GetSelectedSlicerItems) - 2)
End If
Else
GetSelectedSlicerItems = "No items selected"
End If
Else
GetSelectedSlicerItems = "No slicer with name '" & SlicerName & "' was found"
End If
End Function
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Sat 2 Mar 2019: Automatically marked as resolved.
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:Copying Values Without Formulas in Excel 2010If you want to copy the contents of a cell but don't want to copy the formula with it then use the following simple method: |