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

  • Thread starter Thread starter dicko1
  • Start date Start date
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
 
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
Getpivot data dynamic reference 1
Pivot Table with Data Model Connection - getpivotdata #REF error 0
Pivot Table with Data Model Connection - getpivotdata reference issue 0
Use cell_ref in place of an item in get pivot data function 2
GETPIVOTDATA in Excel 2007 2
How do I hide hide #REF! in Excel 3

Back
Top