+ Reply to Thread
Results 1 to 8 of 8

Using IF function together with GETPIVOTDATA

  1. 04-20-2011, 04:26 AM #1
    Kagesen
    Kagesen is offline
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Using IF function together with GETPIVOTDATA

    Hi all,

    I am working with pivottables for the first time in my life and have come across somewhat of a challenge. In the attachment you can sheet my pivottable.

    What I'm trying to set up is a formula that only captures the total value of a specific system.

    The sheet is updated monthly with new numbers so the position of each cell changes and therefore I cannot for example use "GETPIVOTDATA(A6,A21) " to find the total cost for System 01 which is Fire and Gas Detection Total because it will change next month.

    Hope fully someone can help me with my troubles...

    Excel challenge.jpg
    Last edited by Kagesen; 04-20-2011 at 05:39 AM.
    Reply With QuoteRegister To Reply
  2. 04-20-2011, 04:31 AM #2
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Using IF function together with GETPIVOTDATA

    Hi,

    If you pop an = in a spare cell outside the pivot table and then select the total that you want Excel will display the GETPIVOTDATA formula that is required for that for you.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
    Reply With QuoteRegister To Reply
  3. 04-20-2011, 04:45 AM #3
    Kagesen
    Kagesen is offline
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Using IF function together with GETPIVOTDATA

    Quote Originally Posted by Domski View Post
    Hi,

    If you pop an = in a spare cell outside the pivot table and then select the total that you want Excel will display the GETPIVOTDATA formula that is required for that for you.

    Dom
    At first I didn't understand what you meant because when I did what you told, it just shows =A6 for example. I went to PivotTable Tools chose Options and checked the "Generate GetPivotData" so now it is working.

    Thank you so much for the help.

    Regards, Kagesen
    Reply With QuoteRegister To Reply
  4. 04-20-2011, 04:50 AM #4
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Using IF function together with GETPIVOTDATA

    Sorry, thought that was the default setting.

    Dom
    Reply With QuoteRegister To Reply
  5. 04-20-2011, 04:54 AM #5
    Kagesen
    Kagesen is offline
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Using IF function together with GETPIVOTDATA

    I worked out the first challenge I came across with your help maybe you could help me with another. As I said my sheet is updated monthly and some months there are no costs on specific system, so lets say that there are no cost on system 1. If that happens then my sheet returns the value #REF!. I would like it to show just 0 if possible.

    =GETPIVOTDATA("Amount (1,000)",'Output data'!$A$6,"Maintenance Code","01 Fire And Gas Detection","R/M & MOE cost","01 RM") <-- this is the formula. Can I put in ISERR formula somehow?
    Reply With QuoteRegister To Reply
  6. 04-20-2011, 04:58 AM #6
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Using IF function together with GETPIVOTDATA

    Have a look at using the IFERROR function. I think it's something like:

    =IFERROR(GETPIVOTDATA("Amount (1,000)",'Output data'!$A$6,"Maintenance Code","01 Fire And Gas Detection","R/M & MOE cost","01 RM"),0)

    Dom
    Reply With QuoteRegister To Reply
  7. 04-20-2011, 05:04 AM #7
    Kagesen
    Kagesen is offline
    Registered User
    Join Date
    04-20-2011
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2013
    Posts
    54

    Re: Using IF function together with GETPIVOTDATA

    Thank you again for the valuable help you provided me Dom.

    Although it has been showcased how bad my excel skills are I definitely believe I can greatly improve by reading more on this forum.
    Reply With QuoteRegister To Reply
  8. 04-20-2011, 05:06 AM #8
    Domski
    Domski is offline
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Using IF function together with GETPIVOTDATA

    No problem. I know I have

    Dom
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1