Pivot Table Formula - Add Cell from outside table

Posted on 2011-05-05
Last Modified: 2012-06-27
If i have a formula on a pivot table that calculates some results no problem.

I want to add an IF to the formula so that if a field is a value it uses the formula but if it's zero i need it to drop in a value from a cell outside the pivot table.

For example if i had the formula to work out the cost we are paying for advertising, the formula would be

= Cost * Number of Enquiries

If i have a seperately worked out (outside the pivot) cost for the Internet in Cell M4 can i use this in the formula? i.e.

=IF (Source <> "Internet", Cost * Number of Enquiries, Value in Cell M4)

Is this possible?

Question by:carlspywell
    LVL 33

    Expert Comment

    It would help if you post a sample sheet with the Pivot Table...
    LVL 6

    Expert Comment

    Following should work:
    =IF(D2<>"Internet", GETPIVOTDATA("Marks",$B$11,"Name","A"), $M$4)

    See an example is attached. See the formulas in Column E with heading "Formula column".

    Author Comment

    I need it the other way round. I need to pull the information from M4 into the result on the pivot table if source is internet.

    If you look at the attached sample, I have the cost for all other Sources from the data. Internet is a bit more complicated and is based on a monthly fee plus 1-5 enquires at so much, 6-10 at another rate etc. (i've not included this formula just the total in M4)

    The Total Calculation is - =IF(internet= 0,Cost, "Value in M4")

    I need it to put the Value in M4 in if "Internet?" equals 1

    LVL 6

    Accepted Solution

    Hmm.. the values inside the pivot table are computed by Excel internally using GETPIVOTDATA(). I donno if you can change that.
    It would be much easier of course to change your source data.
    Sorry but I didn't quite understand your usecase.

    What I've done now is added a new column to your data set called "New Cost". This has the value from M4 if the source is Internet, else it uses the normal cost.
    Now use this column in the pivot table.

    See attached.

    Does this help?

    Author Comment

    It's not an easy one to solve!!

    I was thinking along those lines but really complicated for my project as the data is externally generated.

    You can manipulate the data in the pivottable calculation using "IF" statement (see ScreenCast) :

    =IF(internet= 0,Cost, 100)

    would put the value of 100 in if Internet was not = 0

    I need to know if the 100 can be replaced by the value in a cell outside the table.


    LVL 6

    Expert Comment

    Sorry but how is what you've finally chosen different from what I suggested in the post 35697216?
    "What I've done now is added a new column to your data set called "New Cost". This has the value from M4 if the source is Internet, else it uses the normal cost."

    Author Comment

    Point Taken @theKashyap. However, your solution does not work in my situation but as it would work if the data source was within the spreedsheet, i'll award the points.

    My solution is different because:

    If you read my response, your solution added a column to the data source! I cannot add a column to the data source as the data source is external and cannot be manipulated from the spreadsheet.

    In the attached sample, I used the data from the cells "Source", "Enquiries" & "Cost" to create a new column "Solution" outside the table, and used the formula "=IF(A12="Internet",M4,C12*B12)"  to give the result.
    If Souce = Internet it uses M4 otherwise it Calculates the result by multiplying the Enquiries by the Cost.

    Thanks for you input.


    Author Closing Comment

    Not the full solution to my scenario but would work if data source was within the spreadsheet and not external.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now