If statement - blank cells

[Shukis7]

Hi all,

I'm trying to use the if statement to check for blank cells and then do something if the cell is blank, and if not then leave the cell as is.

Below is the formula I'm currently using.

=IF(AP2="",IF(ISNUMBER(SEARCH(Sheet1!$B$2,BF2)),"Apartment",IF(ISNUMBER(SEARCH(Sheet1!$B$3,BF2)),"House",IF(ISNUMBER(SEARCH(Sheet1!$B$4,BF2)),"Office",IF(ISNUMBER(SEARCH(Sheet1!$B$5,BF2)),"Shop",IF(ISNUMBER(SEARCH(Sheet1!$B$6,BF2)),"Shop"," "))))),AP2)

Note that without the first part IF(AP2="" the formula is working perfectly. However, when I want it to apply only on blank cells it doesn't seem to work and for every cell it returns a zero.

I would appreciate any help.

Thank you!

 

[FormR]

Hi, try like this instead.

=IF(AP2="",IF(ISNUMBER(SEARCH(Sheet1!$B$2,BF2)),"Apartment",IF(ISNUMBER(SEARCH(Sheet1!$B$3,BF2)),"House",IF(ISNUMBER(SEARCH(Sheet1!$B$4,BF2)),"Office",IF(ISNUMBER(SEARCH(Sheet1!$B$5,BF2)),"Shop",IF(ISNUMBER(SEARCH(Sheet1!$B$6,BF2)),"Shop"," "))))),"")

 

[Shukis7]

Hi thanks for the reply, it's still giving me zero for all cells for some reason.

 

[FormR]

Then maybe the cell is not really blank - what does this formula return?

=AP2=""

when applied to one the seemingly blank cells.

 

[Shukis7]

It's giving me 0. Does that mean the cells are not really blank? I've deleted everything in those cells, should I do like clear all?

 

[Shukis7]

My bad, it's returning TRUE so the cells are indeed blank. The problem is that I'm referring to the own cell. I'm trying to apply the formula to AP2 and AP2 is one of the variables in the formula.

 

[FormR]

I'm trying to apply the formula to AP2 and AP2 is one of the variables in the formula.

You can't do that as it's circular.

But it doesn't make any sense - if the formula you have already returns a blank, why are you trying to wrap it in another if() statement to say return a blank when it has already returned a blank??

 

[Shukis7]

So basically there are two columns that I want the isnumber(search) formula to look into. The first is BH so I did that and worked. Then I wanted to look into the BF columns, but only for the cells that remained blank after the first formula. But know I understand that this is not possible, so should I combine both in one big formula? Thanks for your time btw.