Don't want !ref displayed when source worksheet has not data.

D

dicko1

I have a formula the multiplies two cells and adds that product to a
referenced cell from another worksheet.

For example:

=T9*L14+GETPIVOTDATA("Billing Amount",'Labor
Pivot'!$A$6,"TradeCode","F04")

The problem is that when the Pivot Table is changed, the data that the
formula is looking for may not be there anymore and the formula cell
displays #REF!. Is there a way for my formula to still display the
product of T9*L14 and if no data can be found from the Pivot Table it
ignores that part of the formula or equates it to 0?

Thanks,
Ryan
 
G

Guest

You could try ands check for the error with an if staement perhaps.
something like:

=IF(ISERR(your formula here),T9*L14,put your formula here)

It should calculate your formula and if the result is going to be #REF it
will multiply your two cells only and show that. If it is not an error then
it will show the full calculation. Does that make sense?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do not want formulas to reference old workbook. 10
GETPIVOTDATA #REF! error XL2003/2010 1
Use cell_ref in place of an item in get pivot data function 2
Pivot Table with Data Model Connection - getpivotdata #REF error 0
Conditional Formatting Question 13
Pivot Table Data - Troubleshoot needed 4
Pivot Table with Data Model Connection - getpivotdata reference issue 0
Pivot Table Formula not multiplying correctly 2

Top