IF(FIND Formula gives #VALUE! error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a brick selection sheet with Data Validations in Lists of Brick Blends.
I have added * to the end of each brick blend type to signify the amount of
different bricks ie. Colonial Blend** means 2 x different bricks required
(Non Chargeable to client) and Homestead Blend*** means 3 x Different Bricks
required. (Chargeable to client)

I have tried to write a formula that puts an X in a cell adjacent to the
Data Validation cell (means Chargeable to client) for 3 brick blend and if it
is anything else leave the cell blank. I keep getting the #VALUE! Error.

For Example:- =IF(FIND("***",J19,1)>0,"X","")
It works fine putting the "X" value in the adjacent cell, but when I want it
to return a value of nothing ("") it gives me the error.
 
ther werre two things wrong. first the * ws being treated as a wild carrd
character not a real *. Need to switch to Search to handle the *. the ~
tells search tto look for the * (not a wild carrd). Second problem if ***
wasn't found a value error is returned from both find and search. had to add
ISNUMBER to eliminate the error that occured when *** was not found.

=IF(ISNUMBER(SEARCH("~*~*~*",D8,1)),"X","")
 
I don't think you want the tilde character if you use =Find(). But when you
switched to =search(), it was required.

(I thought it was interesting.)
 
Thanks Dave, Much appreciated.
I'm just an amateur at this, and guys like you are worth their weight in gold.
The girls at work will be most impressed when I tell them "I" figured it out
LOL.
Thanks again mate.
Grant.
 
Back
Top