Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Thursday, 20 June 2013

Replacement function for GETPIVOTDATA

Why is GETPIVOTDATA so cumbersome to work with!  I'm always having to do stuff like:
=if(iserror(GETPIVOTDATA([parameter set])),0,GETPIVOTDATA([parameter set]))

It gets to be a real pain as I have to edit both [parameter set]s to have it work properly and there's always the possibility of making a mistake that is not immediately apparent.

I finally decided I'd create my own function that returned 0 instead of erroring:

Function GETMYPIVOTDATA(data_field, pivot_table, _
    Optional field1, Optional item1, _

    Optional field2, Optional item2, _
    Optional field3, Optional item3, _
    Optional field4, Optional item4, _
    Optional field5, Optional item5, _

    Optional field6, Optional item6, _
    Optional field7, Optional item7, _
    Optional field8, Optional item8, _
    Optional field9, Optional item9, _

    Optional field10, Optional item10, _
    Optional field11, Optional item11, _
    Optional field12, Optional item12, _
    Optional field13, Optional item13, _

    Optional field14, Optional item14)
Dim retval
On Error Resume Next
retval = pivot_table.pivottable.GetPivotData(data_field, _
    field1, item1, field2, item2, field3, item3, field4, item4, _
    field5, item5, field6, item6, field7, item7, field8, item8, _
    field9, item9, field10, item10, field11, item11, field12, item12, _
    field13, item13, field14, item14 _
On Error GoTo 0
GETMYPIVOTDATA = IIf(IsError(retval), 0, retval)
End Function

Now I just use =GETMYPIVOTDATA([parameter set])

No comments:

Post a Comment