How to determine if a column contains any text, if so return "in use"

[sipa]

Hello, I've tried several examples of formula's to try to get this to work but am failing to find a solution, although I'm sure it is relatively simple.

I have a column that in some cells contains a single text character, if text is found anywhere in the column then I want to return "in use".

hope you can help

thanks

[quekbc]

Try...

Please Login or Register  to view this content.

[sipa]

This is what I've used =IF(COUNTA(AO3:AO197)=0,"", "In use")

Still returns "in Use" if the column is blank

[hoyasaxa215]

Try this:

Please Login or Register  to view this content.

[sipa]

Hello

Using =IF(ISBLANK(AO3:AO197)=TRUE,"","In Use") Still returns "In use" if the column is blank

I should mention the entire column does contain formulas that return a single text symbol if true, maybe the column LOOKS blank but isn't ?

[hoyasaxa215]

Don't put the entire range in your formula (AO3:AO197). Just put the SPECIFIC CELL you're checking. In the column to the right of AO3, place this formula and copy it down:

Please Login or Register  to view this content.

[sipa]

I need to check the range of the columns

[Tony Valko]

I should mention the entire column does contain formulas that return a single text symbol if true, maybe the column LOOKS blank but isn't ?

Show us what some of those formulas look like. Do they return formula blanks "" ? If so, maybe this is what you need:

=IF(COUNTIF(AO3:AO197,"?*"),"In Use","")

[sipa]

Here is an example of the formulas used, it's an array formula

=IF(ISNUMBER(MATCH($AO$2&$AU$2&$C3,'GPlan'!$C$18:'GPlan'!$C$301&'GPlan'!$D$18:'GPlan'!$D$301&'GPlan'!$E$18:'GPlan'!$E$301,0)),'GPlan'!$E$15,"")

[sipa]

Tony, your adapted formula did the trick.

Thanks again for you help

[Tony Valko]

Here is an example of the formulas used, it's an array formula

=IF(ISNUMBER(MATCH($AO$2&$AU$2&$C3,'GPlan'!$C$18:'GPlan'!$C$301&'GPlan'!$D$18:'GPlan'!$D$301&'GPlan'!$E$18:'GPlan'!$E$301,0)),'GPlan'!$E$15,"")

OK, so the formula could return formula blanks.

Try the formula in post 8.

It will exclude (not count) cells that contain formula blanks. Formula blanks evaluate as TEXT values.

[sipa]

That did it Tony, thank you.

[Tony Valko]

You're welcome. Thanks for the feedback!