Formula required to pick up if another cell returns a certain value

[Soares79]

Hi,

I need a formula to return a certain value if another cell returns a certain value. I can see heaps of formulas online, but this seems to be only if the cell has text in it. My cell has a formula picking up results from another sheet.

So I need a formal in Column D to say "Open" or "Closed" is the formula in G returns a value that contains the letters "CONTIN". The returned value on Column G will show a result of "AEC ANZ-CONTIN-002528", but this number will keep changing, only the element "CONTIN" is consitent.

Hope this makes sense, but all thng i have tried seems to look at the formula in Column G, not the returned value, so I cant make it work.

Thanks,

Rod.

 

[Joe4]

So, if you want the formula in D1 to say "Open" if the word "CONTIN" appears in cell G1, and you want it to say "Closed" if it does not, put this formula in cell D1:

=IF(ISNUMBER(SEARCH("CONTIN",G1)),"Open","Closed")

 

[jasonb75]

Unless you use the 'FORMULATEXT' function any formula will look at the result that you seen in the cell, not the underlying formula.
Certain formatted values such as dates may cause some confusion if you were trying to search for a month by name, but there should be no such issues with what you have.

 

[Soares79]

Ha! brilliant, thanks so much guys. I had a very similar formula written but it wouldn't work - I had the g1 cell reference and the "CONTIN" the wrong way around.

Just to add a little more complexity, can I add some more search field? Over and above "CONTIN", can I also see if it says "received" or 'RTRFI". All three words would still result in Closed appearing.

 

[Soares79]

Ha! brilliant, thanks so much guys. I had a very similar formula written but it wouldn't work - I had the g1 cell reference and the "CONTIN" the wrong way around.

Just to add a little more complexity, can I add some more search field? Over and above "CONTIN", can I also see if it says "received" or 'RTRFI". All three words would still result in Closed appearing.

Dont worry, figured it out. Thanks!