#REF Error...

[Smonczka]

Is there a formula that will return a value of "0" if referencing a
cell that has "#Ref" in it?

In other words something like ISNA for #Ref. I have several tables
that are linked to a summary page. The tables pull from an SQL back
end and depending on the query may return no values. When this happens
the summary pages errors because of a #Ref error (ie. no data).

Any ideas out there?

Thanks all,

Steve Monczka

 

[VancitysFinest]

Use an IF statement with the ISERROR function.

Victor Lai

 

[Bob Phillips]

Try IsError, it will catch any error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Pete_UK]

Yes, but #REF usually means a bad reference, rather than no data - if
you have a formula refering to A1 and then delete column A or row 1
this will happen. Using ISERROR will help you to trap it, but there may
be something more fundamentally wrong that is causing it, rather than
just missing data.

Hope this helps.

Pete

 

[Smonczka]

Thanks all for the data. That's exactly what I needed.

Pete what happens is that the pivot table is based off of an SQL table.
The Pivot can be filtered by date and sale catagory. If there were no
sales in that catagory for that data then the table shows no data. SQL
OLAP tables can not be configured to "show items with no data" like you
would normaly be able to. So my summary page, the one based off the
pivot table data ends up giving me a #Ref error.

Thanks for the help guys.

Steve