Avoiding #Value errors when referencing DB Tables

[Jeff Metcalf]

I have worksheet that has about 10 columns of data returned from a SQL DB
query.
On the right side of the DB query is a column for Quantitys, and on the
right of the DB query I have some formulas that calculate the cost, MSRP and
other pricing models. It all works well. The formulas that calculate the
pricing model is:

=B2427*Table_Query_from_commissions[[#This Row],[MFRPrice]]
etc

The problem is that the DB query will return more and more rows as products
are added to our CRM database. When I just copy the formulas down from the
worksheet,
=B2427*Table_Query_from_commissions[[#This Row],[MFRPrice]]
If there is no data in #This Row, then I get #Value! errors, and the Summing
operations also return #Value! Errors.

By the same token, if I have 2000 rows in the query today, and 2001
tomorrow, then product number 2001 won't get used in the worksheet
calculations as the rows are not in the section of the workbook that is doing
the calculations....

Is there a way to work around this limitation and have the worksheet add the
formulas on the end if there is data in the row? As in:

If CurrentRowCurrentCell <> null then
=[Column B This row]*Table_Query_from_commissions[[#This Row],[MFRPrice]]
Else 0
?

Thanks in advance.

 

[JLatham]

Try wrapping the formula in an 'error trap' as

=IF(ISERR(B2427*Table_Query_from_commissions[[#This
Row],[MFRPrice]]),0,B2427*Table_Query_from_commissions[[#This
Row],[MFRPrice]])

Remember that should be one long formula, not actually broken into separate
lines as the editor does here. This should give you a zero instead of
#Value! when #Value would have been shown, otherwise it returns the result.
ISERR() actually traps for any type of error except #N/A!, so it also hides
things like #DIV/0!. Sometimes that side effect isn't good because it can
hide errors that would indicate some situation that you could correct rather
than ignoring.

 

[Jeff Metcalf]

Thanks, I'll give that a shot.

Try wrapping the formula in an 'error trap' as

=IF(ISERR(B2427*Table_Query_from_commissions[[#This
Row],[MFRPrice]]),0,B2427*Table_Query_from_commissions[[#This
Row],[MFRPrice]])

Remember that should be one long formula, not actually broken into separate
lines as the editor does here. This should give you a zero instead of
#Value! when #Value would have been shown, otherwise it returns the result.
ISERR() actually traps for any type of error except #N/A!, so it also hides
things like #DIV/0!. Sometimes that side effect isn't good because it can
hide errors that would indicate some situation that you could correct rather
than ignoring.

I have worksheet that has about 10 columns of data returned from a SQL DB
query.
On the right side of the DB query is a column for Quantitys, and on the
right of the DB query I have some formulas that calculate the cost, MSRP and
other pricing models. It all works well. The formulas that calculate the
pricing model is:

=B2427*Table_Query_from_commissions[[#This Row],[MFRPrice]]
etc

The problem is that the DB query will return more and more rows as products
are added to our CRM database. When I just copy the formulas down from the
worksheet,
=B2427*Table_Query_from_commissions[[#This Row],[MFRPrice]]
If there is no data in #This Row, then I get #Value! errors, and the Summing
operations also return #Value! Errors.

By the same token, if I have 2000 rows in the query today, and 2001
tomorrow, then product number 2001 won't get used in the worksheet
calculations as the rows are not in the section of the workbook that is doing
the calculations....

Is there a way to work around this limitation and have the worksheet add the
formulas on the end if there is data in the row? As in:

If CurrentRowCurrentCell <> null then
=[Column B This row]*Table_Query_from_commissions[[#This Row],[MFRPrice]]
Else 0
?

Thanks in advance.