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

elitef

New Member
Joined
Feb 3, 2016
Messages
22
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

BlueHornet

Active Member
Joined
Apr 13, 2012
Messages
338
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

New Member
Joined
Feb 3, 2016
Messages
22
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

New Member
Joined
Feb 3, 2016
Messages
22
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,446
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top