IF(FIND Formula gives #VALUE! error
 

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.

IF(FIND Formula gives #VALUE! error
 

One possible fix:
=IF(ISERROR(FIND("***",J19,1)),"",IF(FIND("***",J1 9,1)0,"X"))

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"XR6T4GC" wrote:

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.

IF(FIND Formula gives #VALUE! 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","")

"XR6T4GC" wrote:

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.

IF(FIND Formula gives #VALUE! error
 

Another one:

=IF(ISNUMBER(FIND("***",J19)),"X","")



XR6T4GC wrote:

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.

--

Dave Peterson

IF(FIND Formula gives #VALUE! error
 

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.)


Joel wrote:

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","")

"XR6T4GC" wrote:

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.

--

Dave Peterson

IF(FIND Formula gives #VALUE! error
 

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.

"Dave Peterson" wrote:

Another one:

=IF(ISNUMBER(FIND("***",J19)),"X","")



XR6T4GC wrote:

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.

--

Dave Peterson

IF(FIND Formula gives #VALUE! error
 

Thanks Joel,
As I said to Dave, your help is much appreciated.

Grant.

"Joel" wrote:

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","")

"XR6T4GC" wrote:

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.