asked on

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?

jppinto

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

theKashyap

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".
Example.xls

carlspywell

ASKER

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

Sample.xls

ASKER CERTIFIED SOLUTION

theKashyap

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

carlspywell

ASKER

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.

Thanks

carlspywell-456436.flv

theKashyap

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."

carlspywell

ASKER

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.

Carl
Sample.xls

carlspywell

ASKER

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