How to avoid sheet and cell #REF error when a worksheet with data is deleted

[rdoulaghsingh]

I'm using the formula below to fill in a cell with customer name from two separate sheets in a workbook. If the customer name is entered on one of the sheets, then the cell with the formula fills in the customer name and the other sheet without a customer is deleted. The function below does everything I want, but the problem is when one of the sheets is deleted, I get a #Ref error. Is there any way to avoid this in the formula below?

=IFERROR(IF('Introduction 800-171'!B15<>"<Customer Name>",'Introduction 800-171'!B15,IF('Introduction CMMC'!B15<>"<Customer Name>",'Introduction CMMC'!B15,"")),INDIRECT('Introduction 800-171'!B15))

 

[Zot]

The syntax is

=IFERROR (value, value_if_error)

In your formula above, the value_if_error is set as INDIRECT('Introduction 800-171'!B15) if I'm not mistaken. I believe when you delete, this reference is gone thus showed error. So you need to change your formula to show "" (blank as example)

 

[rdoulaghsingh]

The syntax is

=IFERROR (value, value_if_error)

In your formula above, the value_if_error is set as INDIRECT('Introduction 800-171'!B15) if I'm not mistaken. I believe when you delete, this reference is gone thus showed error. So you need to change your formula to show "" (blank as example)

Thank you for replying. I tried that and it still gives the ref error.

 

[jtakw]

Hi,

My solution given in your previous thread works if the 2 cells in question are on the Same sheet And Next to each other, as you described.
That formula as it is, will Not work if the 2 cells are in Different sheets.

Try this, change the Sheet names and Cell references accordingly:

=IFERROR(IF(Sheet775!D1<>"<Customer Name>",Sheet775!D1,IF(A1<>"<Customer Name>",A1,"")),IF(A1<>"<Customer Name>",A1,IF(Sheet775!D1<>"<Customer Name>",Sheet775!D1,"")))

 

[rdoulaghsingh]

Hi,

My solution given in your previous thread works if the 2 cells in question are on the Same sheet And Next to each other, as you described.
That formula as it is, will Not work if the 2 cells are in Different sheets.

Try this, change the Sheet names and Cell references accordingly:

=IFERROR(IF(Sheet775!D1<>"<Customer Name>",Sheet775!D1,IF(A1<>"<Customer Name>",A1,"")),IF(A1<>"<Customer Name>",A1,IF(Sheet775!D1<>"<Customer Name>",Sheet775!D1,"")))

Interesting! This is what I've come up with based on your formula, but it's still giving me an error that there's a problem with the formula.

=IFERROR(IF('Introduction 800-171'!B15<>"<Customer Name>",'Introduction 800-171'!B15 & "_SafeAttachments",IF('Introduction CMMC'!B15<>"<Customer Name>",'Introduction CMMC'!B15 & "_SafeAttachments","")),IF('Introduction CMMC'!B15 <>"<Customer Name>",'Introduction CMMC'!B15,IF(Introduction 800-171'!B15<>"<Customer Name>",Introduction 800-171'!B15,"")))

 

[rdoulaghsingh]

Hi,

My solution given in your previous thread works if the 2 cells in question are on the Same sheet And Next to each other, as you described.
That formula as it is, will Not work if the 2 cells are in Different sheets.

Try this, change the Sheet names and Cell references accordingly:

=IFERROR(IF(Sheet775!D1<>"<Customer Name>",Sheet775!D1,IF(A1<>"<Customer Name>",A1,"")),IF(A1<>"<Customer Name>",A1,IF(Sheet775!D1<>"<Customer Name>",Sheet775!D1,"")))

Actually...I found a workaround. Thank you for your help once again.