Free Microsoft Excel 2013 Quick Reference

Is this a use for pivot table Results

Hi guys,

1st post here so be gentle.

I am producing graphs that compare last years figures (static) with this years figures (Dynamic) and this years figures come from a Pivot table and Worksheet that has been imported. The value returned in each cell for the graph's dataset is derived from a specific Pivot table row and the column name (month) that matches the column of the data set - so far so good.

The formula currently looks like this.

=IF(ISNUMBER(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),(GETPIVOTDATA("Duration of Spell",'Count PS'!$A$3,"Admission Hospital","Hillside","Discharge Date Month Name",Q3)),0) where 'Count PS' is the name of the worksheet and Q3 is the reference cell that holds the Month Name

The problem is that I will not always be creating the report and other users may use a different name for the imported worksheet. I want to be able to set the name of the worksheet to be referenced in a supporting worksheet so that the user could simply amend the cell value to be the name of the source worksheet, but I can't work out the syntax


Ive looked at several posts and they dont seem to get me to a succesful point. Im trying for the first time to use a dynamic range as the 'source' for a pivot table.

The pivot table and the source data are in two different tabs.

The source data is on tab Log! and i have used Daves formula (i hope correctly) to define the dynamic range. The range starts at Column B and is 22 columns wide:

Refers to:=OFFSET(Log!$B$1,0,0,MATCH(""*"",Log!$B:$B,-1),22)

I have named this dynamic range DMR.

I have a pivot table on another tab. When i try to use DMR as the source (Log!DMR), it gives me an error "Reference is Not Valid".

I must have the syntax wrong somewhere. Any help would be appreciated.

I have a pivot table that i am trying to copy out to another sheet.

A note of clarification.
My variables are used in the sum field - I have 70 different columns of data, that I want to use in combination one column field and one row field. So each time I'm changing the sum calculated by add a new sum and remove the previous one.

I've tried to record the procedure to highlight a new variable and remove the previous. However it doesn't seem to work. Any suggestions gratefully received.

I enclose my code below.

    Dim j As Integer 
    Dim k As Integer 
    Dim i As Integer 
    Dim StrM As String ' new variable
    Dim StrM1 As String ' old variable
    Dim StrN As String 
    Dim theBook As Workbook 
    Dim theSheet As Worksheet 
    Dim theSheet1 As Worksheet 
    Set theBook = ActiveWorkbook 
    Set theSheet = theBook.Sheets("Sheet4") ' this is where my data is
    Set theSheet1 = theBook.Sheets("Sheet3") ' this is where my new table is
    k = 2 ' column in my new table
    j = 1 
    For i = 1 To 70 
        StrM1 = StrM 
        StrM = "SCT43000" & j 
        StrN = "Sum of SCT43000" & j 
        If i < 2 Then 
            theSheet.PivotTables("PivotTable2").AddDataField theSheet.PivotTables( _ 
            "PivotTable2").PivotFields(StrM), StrN, xlSum 
            theSheet.PivotTables("PivotTable2").AddDataField theSheet.PivotTables( _ 
            "PivotTable2").PivotFields(StrM), StrN, xlSum 
            theSheet.PivotTables("PivotTable2").PivotFields(StrM1).Orientation = xlHidden 
        End If 
        theSheet.Range("B4:F637").Copy (theSheet1.Cells(1, k)) 
        j = j + 1 
        k = k + 5 
    Next i 
End Sub 
This Is the macro recorders code 
ActiveSheet.PivotTables("PivotTable2").PivotFields("SCT4300068").Orientation = xlHidden 
 ' this seems to remove the values but not as part of a macro.

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Im having difficulties implementing a pivot table and using dates as column headings.

For Example my data looks like this:

Animal Price Date
Dog £200 10/08/2007
Cat £150 09/08/2007
Horse £350 07/08/2007
Rabbit £80 08/08/2007
Toad £20 06/06/2004
Donkey £10000 05/01/2007
Dog £200 01/01/2005
Cat £150 29/05/2006
Horse £350 3/03/2003
Rabbit £80 12/08/2007
Toad £20 04/09/2007
Donkey £10000 11/09/2007

What I want to do is have a pivot table with column headings such as '1 Day Old' 'Between 1 week and 1 month old' 'Over a month old' and 'over a year old' and show the correspondings figures with Animal in the row of the pivot table.


Column: '1 day old' etc etc
Row: Animal
Data: Count of price


I have a spreadsheet with items and their maintenance dates. Each item may be listed multiple times with various dates. I want to create a pivot table that will show the most recent and previous maintenance date. The most recent is easy by using the built in "max of" in the Pivot table Wizard.

Can someone tell me how to use the "Large" function in a pivot table? or is there a better way to get the 2nd largest value? Auto Merged Post;

Bummer! No answers yet. I thought there might be some experts out there that would have a really tricky way of doing this.

From the Excel help it looks like this isn't possible:

" In formulas you create for calculated fields and calculated items, you can use operators and expressions as you do in other worksheet formulas. You can use constants and refer to data from the report, but you cannot use cell references or defined names. You cannot use worksheet functions that require cell references or defined names as arguments, and you cannot use array functions."

I think I may end up adding a column to the original spreadsheet to create the 2nd largest value and use it in the pivot table.

Other ideas welcome!

I operate a small music management company and I run my accounts using relatively complex Excel Workbooks with different sheets for purchase and sales ledgers, cash in and cash out etc etc. Each financial year has its own workbook. My problem is creating financial reports. For example, if I need to create a report for outgoings and incomings relating to a particular project (a concert tour for example), and if that project was split over several financial years, I find myself spending hours creating pivot tables, and then pivot tables based on other pivot tables etc etc, to just get a clean report on that particular project. What I would love to have is a single report generating system, where I can just enter the name of the project, and get a financial report at the click of a button, based on all the raw data I have painstakingly entered in the individual workbooks and sheets. I reckon I'm too busy to sit down and tackle VBA programming myself, so I wondered if anyone knew of any third party software that might help me realise my dream simply and easily. Someone has already mentioned Crystal Reports, but I don't know whether this will be overkill.

Thanks very much in advance for your help.

Hello Everyone,

I have been able to make this code work fine that I found here on Ozgrid!

I have two columns that I need to put into a pivot table using this code.

Here is a sample of what the data looks like in the two columns

Column A

Rohla 2-7 Oil Tank 1
LoEva #2-23 Oil Tank CFLP 27300-1
Somers Central Oil Tank #1 (22257)

Column B

% Full Ranking
30 to 50
1 to 30
30 to 50
1 to 30
1 to 30
30 to 50
30 to 50
1 to 30
1 to 30
1 to 30

Sub MakeTable() 
    Dim Pt As PivotTable 
    Dim strField As String 
     'Pass heading to a String variable
    strField = Selection.Cells(1, 1).Text 
     'Name the list range
    Range(Selection, Selection.End(xlDown)).Name = "Items2" 
     'Create the Pivot Table based off our named list range.
     'TableDestination:="" will force it onto a new sheet
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ 
    SourceData:="=Items2").CreatePivotTable TableDestination:="", _ 
     'Set a Pivot Table variable to our new Pivot Table
    Set Pt = ActiveSheet.PivotTables("ItemList") 
     'Place the Pivot Table to start from A3 on the new sheet
    ActiveSheet.PivotTableWizard TableDestination:=Cells(3, 1) 
     'Move the list heading to the Row Field
    Pt.AddFields RowFields:=strField 
     'Move the list heading to the Data Field
    Pt.PivotFields(strField).Orientation = xlDataField 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
How do I change this code accordingly to include column B? I can get this to work perfectly for one column.



hey all, I'm having some difficulty in making my pivot tables into a useful printed report - Excel does not seem to allow the user to summarize what criteria has been selected in the report.

Let me provide an example.

If I have data for sales of green, yellow, and red cards for 2005 2006 and 2007, I can make a master pivot table that has all this data. Then, if the manager of red car sales needs a report, I want to select just the red and print that. However, there's no command to pull the fact that I have selected only "red" under the cars type. This is a simplistic example of a larger problem - on a pivot table with a lot of criteria, it really diminishes the usefullness of a table if you can't automatically show what you selected in the header of the document.

I've seen this done for lists via VBA but I can't find a piece of code that will work with a pivot table. Can anyone help?

Hi All

Not so much of a problem as I am getting the result I need, just advice really of better way of working.

All I am doing is counting text values in a table.

The table has 3 main columns(which are relevant to this thread anyway).

Shift - Area - Status

The example I have attached shows examples of DCOUNTA, SUMPRODUCT and a Pivot Table.

I have read many threads stating that the best one to use is Pivot Table followed by DCOUNTA followed by SUMPRODUCT.

The most effective for me seems to be SUMPRODUCT (although this does slow excel down dramatically when you use a lot of these formulas). As do Array Formulas

The Pivot Table does not update on its own, therefore constantly needs to be refreshed. (I could use code to do this)

The DCOUNTA seems to be the least effective at doing what I want (unless I am doing something wrong)

In the attached example can the DCOUNTA be used more efficiently as I don't like the fact that I am duplicating rows to apply the criteria for a different shift. e.g

Area 1 - Late Shift - Banned
Area 1 - Early Shift - Banned

I want my table to be as follows (as the SUMPRODUCT shows)

AREA - Early Shift - Late Shift - Night Shift - Area Total
Area 1
Area 2
Area 3
Area 4
Area 5

Shift Total

Hope all this makes sense.



I have two questions about dealing with formulas in pivot tables and how to make them dynamic. First let me give you some further background.

My database countains the following variables: Country, Company, Period, Product, Category and Sales. Out of this database i created a pivottable which sums the total sales amount for each company in every period. Further selection on country and category is possible in this pivot table. See my attached file!

I want to express the sales of each company as a percentage of the total sales in that period. For example for company A in period 1 their total sales was (929/3172) 29,3% of the total market sales. Which formula do i have to use in my pivot table to express company's market shares?

Further my pivot table can be specified more detailed by country and category. I want to be able to select on country and category in such a way that the company sales are still expressed as a percentage of the market sales. In other words how can i make my pivot table dynamic?

I hope i explained my situation well. If not, don't hesitate to ask me. Thanks again!

I am trying to build some reports by using Pivot Tables, but got to a point I can't progress. I couldn't find a solution by searching the web, so I decided to join the forum and ask for your help.

When selecting an option from the dropdown menu on the page field, the table will change its format to make each column with figures to the same length as the column with the page field. This means that if the pivot table is using several columns, these will immediately expand and will reduce the visibility as a whole.

I was trying to associate a change event macro that once a selection is done I could set the width of the columns, but didn't managed to get working either. Excluding creating a user macro (with button) what can I do to either:
- avoid the pivot table from expanding the column width; or
- get a macro to run every time a selection is made?

Thanks in advance for your help.

Hi there,
I am trying to enter a formula into a cell using a commandbutton.

I am attaching the file and the problem should be easy to follow.

This is the code that I have on my command button.

    Dim MyRange As Range 
     'To highlight the Cell the Vlookup formula should be in
    Set MyRange = Application.InputBox _ 
    (prompt:="Highlight the cell", Title:="Range for Pivot table", Type:=8) 
     ' This is where the program stops running.
    [COLOR="Red"]ActiveCell.FormulaR1C1 = "=VLOOKUP($B7,Sheet2!$A$1:$I$8,6,FALSE))"[/COLOR] 
     'the rest of the cells that needs to be filled down
    Set MyRange = Application.InputBox(prompt:="Select the column to fill", Title:="N N S Column", Type:=8) 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Please help me find a solution for this issue. THanks ahead of time.


This is an easy one for most of you.

Below is my code to create a pivot table. The data is located on a 2nd sheet named 'Data2' in columns B & C.

As you can see the range is already defined. How can i have this as a variable which holds the current address of the automatically selected - occupied cells in columns b-c???

So in order a macro that can..

1) select all occupied cells in colums b-c
2)paste this range as an address in d1
3) assign a variable which has the range for the pivot table macro to complete its work.

Thanks in advance

     ' Macro8 Macro
     ' Macro recorded 05/05/2006 by markb
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ 
    "Data2!R1C2:R27C3").CreatePivotTable TableDestination:= _ 
    "'PivotReport'!R18C2", TableName:= _ 
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10 
    ActiveWorkbook.ShowPivotTableFieldList = True 
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Products") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ 
    "PivotTable1").PivotFields("QTY"), "Sum of QTY", xlSum 
    ActiveWorkbook.ShowPivotTableFieldList = False 
    Application.CommandBars("PivotTable").Visible = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi Everybody,

I found a piece of code that should auto update a pivot table everytime the sheet is activated. However, it does not seem to work, could this be because it does not corresspond with excel2003? I have the following sub in a seperate module.

    Application.OnSheetActivate = "UpdateIt" 
End Sub 
Sub UpdateIt() 
    Dim iP As Integer 
    Application.DisplayAlerts = False 
    For iP = 1 To ActiveSheet.PivotTables.Count 
    Application.DisplayAlerts = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thanks a lot!

rgds, S.

hi, i am viewing my pivot table in Access, there are no grid lines...
which is kinda painful when there are numbers all over the place.
is there a way to insert grid lines.

actually i can see very vague grid lines as defaults. are there ways to change the color?

i want to achieve this using VBA codes. thanks a lot in anticipation.


I hope i dont lose you will the length of this screed..........

I am currently working on a basic database in excel which uses 1 pivot table in order to analyse the data. (It just counts the amount of times a certain criteria hs been entered), simple stuff I know. however here the rub:

The data, although in 1 table is analysed according to the specific shift i wish to look at i.e red, Blue, green or all 3 at once. I have done this by using the following gereric code for each shift, and all i do for each shift is toggle this visible property for the relevant feilds you i can just see the shift i want.

     ' update_pivot Macro
     ' Macro  20/09/2004 by G Davies
    On Error Resume Next 
    Application.ScreenUpdating = False 
     'clear HUD screen
     'update pvt
     'reset fields
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Shift") 
        .PivotItems("Red").Visible = True 
        .PivotItems("Blue").Visible = True 
        .PivotItems("Green").Visible = True 
    End With 
     'format pivot to shift
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Shift") 
        .PivotItems("Red").Visible = True 
        .PivotItems("Blue").Visible = False 
        .PivotItems("Green").Visible = False 
    End With 
     'transfer to inert HUD Sheet, set print area
    ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
    ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address 
     'insert Key to end of sheet
     'Application.Run "'teamsheet V1.0a.xls'!Find_end_of_HUD"
     ' format
    CommandBars("TDMS").Visible = True 
    Application.ScreenUpdating = True 
     'Exit Sub
     '   MsgBox "There is no relavent data availible for this shift.", vbOKOnly, "Error!"
     '  Sheets("Top Red Shift").Select
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

As you can see, i am currently using on error resume next, and you can also see that i have some domant errorhandling code from previous attempts (errhand).

I must point out although this code works quite well at present, if there is no data for one of the three shifts in the pivot table the code errors out even if i run the specific code for a shift with data.
The only way around it i have found is by using On error resume next. But this just gives me, and quite correctly too, a blank pivot table when idealy i want a msgbox saying 'No data....' for the shift with no data whilst still letting me run a view pivot tables for the shifts with data.

I hope i haven't lost you there!

Any ideas?

Thanks in advance!

Hi everyone

I am working on a report using a pivot table that has a large number of staff on it.

We have different grades of people (which represents pay rates)
A person must be working for a 3 month period before they can move onto the 2nd rate.
What I would like my pivot table to show is people that are still on the 1st grade and have been working longer then 3 months.

Is there a way to do this??

Thanks everyone


Using XL97...

My underlying data (for my pivot table) contains a date column. This column is formatted as 'dd-Mmm-yy'. When I sort the underlying data sheet on the date column, all works well. However when I sort the date field on my Pivot table, sorting does not give me the correct results and I get dates listed in incorrect order e.g.
and so on

I have tried defining the same date format on my Pivot's date field but the problem remains. Any clues???

I found this great bit of code to update all Pivot Tables Filters based on a single selection at

    Dim wsMain As Worksheet 
    Dim ws As Worksheet 
    Dim ptMain As PivotTable 
    Dim pt As PivotTable 
    Dim pfMain As PivotField 
    Dim pf As PivotField 
    Dim pi As PivotItem 
    Dim bMI As Boolean 
    On Error Resume Next 
    Set wsMain = ActiveSheet 
    Set ptMain = Target 
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
    For Each pfMain In ptMain.PageFields 
        bMI = pfMain.EnableMultiplePageItems 
        For Each ws In ThisWorkbook.Worksheets 
            For Each pt In ws.PivotTables 
                If ws.Name & "_" & pt  wsMain.Name & "_" & ptMain Then 
                    pt.ManualUpdate = True 
                    Set pf = pt.PivotFields(pfMain.Name) 
                    bMI = pfMain.EnableMultiplePageItems 
                    With pf 
                        Select Case bMI 
                        Case False 
                            .CurrentPage = pfMain.CurrentPage.Value 
                        Case True 
                            .CurrentPage = "(All)" 
                            For Each pi In pfMain.PivotItems 
                                .PivotItems(pi.Name).Visible = pi.Visible 
                            Next pi 
                            .EnableMultiplePageItems = bMI 
                        End Select 
                    End With 
                    bMI = False 
                    Set pf = Nothing 
                    pt.ManualUpdate = False 
                End If 
            Next pt 
        Next ws 
    Next pfMain 
    Application.EnableEvents = True 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would like to know if it is possible for the code to be amended so that:
1. The code updates a single worksheet in an Excel file rather than the entire workbook of Pivot Tables.
2. I am able to select a specific set of Pivot Tables on the worksheet, and another set on the same worksheet. (e.g. I select the north region in one set of tables and the south region on the other set)

I am using Excel 2007


I have been able to build a hierarchy into
the scenario file ( see comboboxes next to
pivot page fields in sheet one pivot )
Thanks to Stephen Bullen.

What i would like to do is to determine a level of hierarchy for which the pivot table scenario should be applicable.

And i would also be able to set the double level. I thought myself by using the select from listbox option, see button
display dialog.

Briefly, i wish to choose what i scenario, not all page and field items should be multiplied with the scenario percentages, only the selections.

Is this possible with the things i added ?
Maybe by using calculated fields from the
cells that the select listbox returns?
I wish calculated fields in the end anyway,
because i do not want to keep the scenario outcome as data in the named range database, because it will increase the amount of data too much.



No luck finding an answer? You could always try Google.