Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If "text" exist within "cell" then TRUE
I cant find a way to look up if a string of text exist within a cell and reply with TRUE or FALSE, such as "if "text" exist somewhere within "cell" then TRUE". I have a list that looks something like: Archetype Skill Dancer Acrobatics, Dance, Seduction Repairman Mechanics, Technology, Security And a cell where I can define an Archetype, such as: A1=Dancer I am trying to create a formula that's something like: If "Dance" exist somewhere in "1 column from the archetype declared in A1", then type the text "Good at dancing" here, else "Cannot Dance" -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
#2
|
|||
|
|||
maybe:
=IF(CONTIF(A:A,"*Dancer*"),"Good at dancing","Cannot Dance") Regards, KL "JemyM" wrote in message ... I cant find a way to look up if a string of text exist within a cell and reply with TRUE or FALSE, such as "if "text" exist somewhere within "cell" then TRUE". I have a list that looks something like: Archetype Skill Dancer Acrobatics, Dance, Seduction Repairman Mechanics, Technology, Security And a cell where I can define an Archetype, such as: A1=Dancer I am trying to create a formula that's something like: If "Dance" exist somewhere in "1 column from the archetype declared in A1", then type the text "Good at dancing" here, else "Cannot Dance" -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
#3
|
|||
|
|||
=IF(COUNTIF(A1:B100,"*Dancer*"),"Good at dancing","Cannot Dance")
-- HTH Bob Phillips "JemyM" wrote in message ... I cant find a way to look up if a string of text exist within a cell and reply with TRUE or FALSE, such as "if "text" exist somewhere within "cell" then TRUE". I have a list that looks something like: Archetype Skill Dancer Acrobatics, Dance, Seduction Repairman Mechanics, Technology, Security And a cell where I can define an Archetype, such as: A1=Dancer I am trying to create a formula that's something like: If "Dance" exist somewhere in "1 column from the archetype declared in A1", then type the text "Good at dancing" here, else "Cannot Dance" -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
#4
|
|||
|
|||
Thanks both of you for your reply... I am using FINDROW to detect the cell that need to be compared with. A36 contains the word "Dancer" $A$542:$I$564 is the field where all data exist. First column contains the profession, and the 9th column from that contains the skills. When I type: =FINDROW($A$36;$A$542:$I$564;9) in cell J551, then I get the skills, such as Dance etc, listed in that cell. That works. If I use =IF(COUNTIF(J551;"*Dancer*");"Is a Dancer";"Is not a Dancer") then I get "Is a Dancer. So far everything works. However, when I try to replace J551 with the FINDROW line I am met with an error: =IF(COUNTIF(FINDROW($A$36;$A$542:$I$564;9);"*Dance r*");"Is a Dancer";"Is not a Dancer") I only get "Error in formula" and Excel refuses to accept it. :( -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
#5
|
|||
|
|||
I have no idea what FINDROW is, but it looks to return a cell value, whereas
COUNTIF wants the cell reference. So it won't work. As FINDROW seems to be a UDF, maybe you could change it to do it all. -- HTH Bob Phillips "JemyM" wrote in message ... Thanks both of you for your reply... I am using FINDROW to detect the cell that need to be compared with. A36 contains the word "Dancer" $A$542:$I$564 is the field where all data exist. First column contains the profession, and the 9th column from that contains the skills. When I type: =FINDROW($A$36;$A$542:$I$564;9) in cell J551, then I get the skills, such as Dance etc, listed in that cell. That works. If I use =IF(COUNTIF(J551;"*Dancer*");"Is a Dancer";"Is not a Dancer") then I get "Is a Dancer. So far everything works. However, when I try to replace J551 with the FINDROW line I am met with an error: =IF(COUNTIF(FINDROW($A$36;$A$542:$I$564;9);"*Dance r*");"Is a Dancer";"Is not a Dancer") I only get "Error in formula" and Excel refuses to accept it. :( -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
#6
|
|||
|
|||
Oh. :( FINDROW is a direct translation of what the function is called in swedish "LETARAD". It starts to be very annoying with the swedish version so next time I will go for an english one... But ohwell, I might have an alternative. :) Thanks anyway -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
#7
|
|||
|
|||
....or rather install this little thingy called TranslateIT:
http://members.chello.nl/keepitcool/download.html Regards, KL "JemyM" wrote in message ... Oh. :( FINDROW is a direct translation of what the function is called in swedish "LETARAD". It starts to be very annoying with the swedish version so next time I will go for an english one... But ohwell, I might have an alternative. :) Thanks anyway -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
#8
|
|||
|
|||
KL Wrote: ....or rather install this little thingy called TranslateIT: http://members.chello.nl/keepitcool/download.html Regards, KL Oh this is so awesome. :D Thanks! Just what I needed. -- JemyM ------------------------------------------------------------------------ JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945 View this thread: http://www.excelforum.com/showthread...hreadid=467251 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text shown up in other cells everytime a text is entered in 1 cell | Excel Discussion (Misc queries) | |||
Not all of the text is visible when looking at the cell. | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
On click, copy text into another cell - XL2K | Excel Worksheet Functions | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions |