If two different cells not equal to a text value display the text value in a different

[rdoulaghsingh]

I have two cells A1 and B2 which contain the default text<Customer Name>. If either of the two cells (one or the other) contain anything other than "<Customer Name>" I want the text to be displayed in another cell K14.
Below is the excel function I have in K14, but I'm missing something. I tried using the OR operator as well, but that doesn't give me the correct output either. I'm also wondering if there is an additional syntax I can add to the function so if one of the two cells with <Customer Name> gets deleted, it doesn't give an #REF! error. Please help!

=IF(AND(A1<>"<Customer Name>",B1<>"<Customer Name>"), A1, B1)

 

[jtakw]

Hi,

Try this:

=IF(A1<>"<Customer Name>",A1,IF(B1<>"<Customer Name>",B1,""))

 

[etaf]

Edit, as posted -already have an answer

=IF(A1<>"<Customer Name>",A1 IF( B1<>"<Customer Name>", B1,"")

 

[Saba Sabaratnam]

Another solution may be:

=IF(COUNTIFS(A1:C1,"Customer Name")<=1,IF(A1<>"Customer Name",A1,B1),"")

Kind regards

Saba

 

[rdoulaghsingh]

Hi,

Try this:

=IF(A1<>"<Customer Name>",A1,IF(B1<>"<Customer Name>",B1,""))

Thank you for the quick reply. It works! The only thing I am wondering is there a way to not receive an error if one of the cells is deleted? I tried with the If(ISERROR but it doesn't like that.

 

[jtakw]

If one of A1:B1 is deleted, it shouldn't show an error, it should show 0, here's a fix:

=IF(AND(A1<>"<Customer Name>",A1<>""),A1,IF(AND(B1<>"<Customer Name>",B1<>""),B1,""))

 

[rdoulaghsingh]

If one of A1:B1 is deleted, it shouldn't show an error, it should show 0, here's a fix:

=IF(AND(A1<>"<Customer Name>",A1<>""),A1,IF(AND(B1<>"<Customer Name>",B1<>""),B1,""))

I meant the actual cell is deleted and not the entry. If the customer name is entered in A1 I'm going to delete the B column containing "<Customer Name>" and vice versa. I have a VBA script which deletes the unused column, so I don't want it to give #Ref! error when that happens.

 

[jtakw]

Hmmm, ok, this will fix it if B column is deleted and formula cell will retain A value, but if A column is deleted, formula cell will become blank:

=IFERROR(IF(A1<>"<Customer Name>",A1,IF(B1<>"<Customer Name>",B1,"")),"")

 

[rdoulaghsingh]

Hmmm, ok, this will fix it if B column is deleted and formula cell will retain A value, but if A column is deleted, formula cell will become blank:

=IFERROR(IF(A1<>"<Customer Name>",A1,IF(B1<>"<Customer Name>",B1,"")),"")

You sir are a genius @jtakw !!! That was it! Thank you so much for your help! And thanks to everyone else who took the time to respond @etaf @Saba Sabaratnam.

 

[rdoulaghsingh]

Hmmm, ok, this will fix it if B column is deleted and formula cell will retain A value, but if A column is deleted, formula cell will become blank:

=IFERROR(IF(A1<>"<Customer Name>",A1,IF(B1<>"<Customer Name>",B1,"")),"")

Is it possible to retain column B also if column A is deleted instead of showing blank?