Pivot Table Calculated Field based on another Calculated Field

[Yard]

Hi,

I have a calculated field (OSDays) which uses other calculated fields (OSDay1, OSDay2, OSDay3) to produce a result. OSDays reads:

= (OSDay1+OSDay2 +OSDay3 )/SUM(MthD1Test)

where MthD1Test is a data field in the source.

This works fine, but it seems that when the file is closed/re-opened, OSDays becomes (intermittently) corrupted to

= (#NAME!+#NAME! +#NAME! )/SUM(#NAME!)

Other calculated fields in the PT are unaffected, but it seems any calculated field which takes the result of another calculated field is prone to corruption.

I have the option to re-create the calculated fields via VBA on Worksheet_Open, but I'd like to know if there's something I'm missing here, or is it a known bug?

Thanks
Yard

 

[Yard]

bump

Same error again this morning.

 

[Yard]

bumpety bump

However, have got a workaround by recreating the Calculated Fields via a Sub (will probably run this on Workbook_Open).

Code posted here in case anyone else searches for this issue.

(Modify the code to suit your own application, naturally).

It requires:

a worksheet (in my code, called CalcFieldFormulas) with the following data in it:

column A = the worksheet name containing the pivot table
column B = the pivot table name
column C = the calculated field name
column D = the calculated field formula (note this must be entered as a text entry in the cell including the "=" sign at the start, e.g.
'=Sales*100

Happy to receive feedback on making the code better.

Sub CreatePTCalcFields()
 
Dim rngToLook As Range
Dim intCountFormulas, i As Integer
Dim strWorksheet, strPT, strFieldName, strFieldForm, strCurrentForm As String
Dim ThisPTCF As CalculatedFields
Dim x As Object
 
'define the area to look for the formulas and count how many there are
With ThisWorkbook.Worksheets("CalcFieldFormulas")
    Set rngToLook = .Range(.Cells(.Rows.Count, 1).End(xlUp).End(xlUp), .Cells(.Rows.Count, 1).End(xlUp).Resize(, 3))
End With
 
intCountFormulas = rngToLook.Rows.Count
 
Debug.Print "Looking in " & rngToLook.Parent.Name & "!" & rngToLook.Address(0, 0)
 
Application.EnableEvents = False
 
'loop through each row in the range
For i = 1 To intCountFormulas
 
    'get the area to work in, the Field name and the Field formula
    strWorksheet = rngToLook(i, 1)
    strPT = rngToLook(i, 2)
    strFieldName = rngToLook(i, 3)
    strFieldForm = rngToLook(i, 4)
 
    'test if the worksheet exists, and skip the row if it doesn't
    On Error Resume Next
    Set x = ThisWorkbook.Sheets(strWorksheet)
    If Err <> 0 Then
        Debug.Print "Row skipped because worksheet '" & strWorksheet & "' not found"
        GoTo NextRow
    End If
    On Error GoTo 0
 
    'define which set of Calculated Fields in which PivotTable to work with
    Set ThisPTCF = Worksheets(strWorksheet).PivotTables(strPT).CalculatedFields
 
    'skip to adding the name rather than modifying it (in case the name doesn't already exist)
    On Error GoTo CFAdd:
 
    'load the existing formula into a string - if it doesn't already exist then jump to
    'CFAdd and add a new field
    strCurrentForm = ThisPTCF(strFieldName).StandardFormula
    Debug.Print "Current formula for " & strFieldName & strCurrentForm
 
    'test if the current formula is corrupted or doesn't match what it should
    If InStr(1, strCurrentForm, "#NAME?") > 0 Or strCurrentForm <> strFieldForm Then
            'if it is, then modify this field to the formula from the range
            ThisPTCF(strFieldName).StandardFormula = strFieldForm
            Debug.Print strFieldName & " was changed from " & strCurrentForm & " to " & strFieldForm
        Else
            Debug.Print strFieldName & " was left unchanged"
    End If
 
NextRow:
Next i
 
Application.EnableEvents = True
 
Exit Sub
 
'jump to here if a name doesn't already exist and needs to be added
CFAdd:
    ThisPTCF.Add strFieldName, strFieldForm, True
 
     'now go back to the loop and continue
     Resume Next
 
End Sub