Formula #REF issue

[jcaptchaos2]

Is there a way to get a formula that looks at another worksheet to stick even if a row on the other worksheet that the formula pulls from is deleted? For example I have a worksheet with this formula on it, the data on the "Shift 1" worksheet is a table that is pulled from an access database so when someone switches shifts it is changed in that access database. When the table is refreshed that employee is no longer there the formula returens the #REF error. If it just left the formula there it would be fine. The only way I can think of getting around this is to create a macro that places all the correct formulas back in the spread sheet.

='Shift 1'!B3

 

[MrKowz]

Try:

=INDIRECT("'Shift 1'!B3")

I don't like to use INDIRECT when I don't have to, because it is a volatile formula (evaluates EVERY time something calculates), but it is needed here.

 

[jcaptchaos2]

MrKowz,

Thanks for the quick reply, I will give it a try.