 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / April 2007IF(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. One possible fix: =IF(ISERROR(FIND("***",J19,1)),"",IF(FIND("***",J19,1)>0,"X")) Dave  SignatureA hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. > 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 [quoted text clipped - 9 lines] > 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 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 [quoted text clipped - 9 lines] > 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.) > 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 ~ [quoted text clipped - 17 lines] > > 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. SignatureDave Peterson Thanks Joel, As I said to Dave, your help is much appreciated. Grant. > 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 ~ [quoted text clipped - 17 lines] > > 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","") > 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 [quoted text clipped - 9 lines] > 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. SignatureDave 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. > Another one: > [quoted text clipped - 13 lines] > > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.