#REF Error...

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
Mon Jan 30 12:28:22 CST 2006

Use an IF statement with the ISERROR function.

Victor Lai

Bob
Mon Jan 30 12:29:21 CST 2006

Try IsError, it will catch any error.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Smonczka" <smonczka@hotmail.com> wrote in message
news:1138645387.384526.55800@o13g2000cwo.googlegroups.com...
> 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
>

Pete_UK
Mon Jan 30 13:50:39 CST 2006

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
Tue Jan 31 12:39:10 CST 2006

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