[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 591
  • Last Modified:

Pivot Table Formula - Add Cell from outside table

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?

  • 4
  • 3
1 Solution
It would help if you post a sample sheet with the Pivot Table...
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".
carlspywellAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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?
carlspywellAuthor Commented:
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.


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."
carlspywellAuthor Commented:
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.

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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now