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.

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.

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.

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

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.

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

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