How to avoid #REF for formulas referencing table that is updated when file is opened

[dpnab]

Hello,

I have a file that has 2 sheets. The first is a table that is connected to other data via ODBC and is updated whenever the file opens (let's just call it a daily ship log). So if you open it at the end of the day, there are lots of entries in the table. If you open it first thing, it shows nothing, since nothing has shipped yet.

The second sheet has several columns with formulas that reference data in the first sheet, ie =Sheet1!A1. The problem is if there is no data, then the formula replaces A1 with #REF.

Is there anyway to "lock" the formula?

Or do I "link" the cell to the table?

Thanks!

 

[zero269]

Hello,

I have a file that has 2 sheets. The first is a table that is connected to other data via ODBC and is updated whenever the file opens (let's just call it a daily ship log). So if you open it at the end of the day, there are lots of entries in the table. If you open it first thing, it shows nothing, since nothing has shipped yet.

The second sheet has several columns with formulas that reference data in the first sheet, ie =Sheet1!A1. The problem is if there is no data, then the formula replaces A1 with #REF.

Is there anyway to "lock" the formula?

Or do I "link" the cell to the table?

Thanks!

The IFERROR Funciton can be used to hide those errors...

Hide error values and error indicators in cells - Microsoft Support

When your formulas have errors that you anticipate and don't need to correct, but you want to improve the display of your results. There are several ways to hide error values and error indicators in cells in Excel.

support.microsoft.com

IFERROR function - Microsoft Support

How to use the IFERROR function in Excel to trap and handle errors in a formula.

support.microsoft.com