Is there a way to avoid #Ref error if sheet is removed?

[elitef]

Hi, is there a way to avoid the #REF error when the sheet that is being reference is removed?
My formula is as follows:

=IF(D1="mail","",IF(D1="carrier","",IF(I1="","",IF(H1="Active",IF(D1="","",IF(IFERROR(VLOOKUP(C1,BARK!F:F,1,FALSE),"")="","MISSING","")),"")))))

So the BARK sheet is the one that gets removed on a daily basis and then re-added the following day.
I am trying to see how to keep the formula steady without me having to modify the formula to remove #REF and put in BARK every day.

 

[BlueHornet]

This should work:

On the permanent sheet, add a text cell (for this example, let's say at A1) with contents:
BARK!F:F

Then you can refer to that in the VLOOKUP as:
VLOOKUP( C1, Indirect( A1), 1, False)

When the BARK sheet is deleted, you'll still get a REF# error for that condition, but it won't wreck the formula. As soon as BARK is re-added, then the reference will work again, and the formula won't need to be rebuilt.

 

[elitef]

Thank you BlueHornet.
I found the exact same solution on a different thread and was just coming here to update my original post to mark it as SOLVED.

 

[elitef]

Thank you BlueHornet.
I found the exact same solution on a different thread and was just coming here to update my original post to mark it as SOLVED.

As a follow up to anyone interested, there is no need to enter the range of the sheet going to be deleted in a different cell.

VLOOKUP(C1,INDIRECT("BARK!$F:$F",1,FALSE)