Assign value based on another
Posted by Allision on December 07, 2001 2:37 PM
I want to assign a cells values based on whether or not another "contains" a certain string.
For example:
If cell in H:H contains "Dev" then 17 Else
If cell in H:H contains "Pro" then 11 Else
etc.
I'm thinking a select statement would work, but am not certain how to write it.
P.S. I say "contains" Dev because the cell in question could have a value or Dev or Developer, or Learning Developer, etc
Posted by IML on December 07, 2001 2:46 PM
You could use
=IF(ISNUMBER(FIND("dev",LOWER(A1))),17,"ELSE")
for finding dev in cell A1. Good luck.
Posted by IML on December 07, 2001 2:54 PM
save a few key strokes with ...
=IF(ISNUMBER(SEARCH("dev",A1)),17,"else")
Posted by Aladin Akyurek on December 07, 2001 2:54 PM
If VBA is not per se required, one can use:
=IF(SUMPRODUCT(ISNUMBER(SEARCH("Dev",H1:H100))+0),17,"")
=IF(SUMPRODUCT(ISNUMBER(SEARCH("Pro",H1:H100))+0),11,"")
where ELSE is set to "".
The range H1:H100 must be adjusted to the situation (H:H is not allowed in this formula).
Aladin
