=ISTEXT conditional formatting on cells for VISIBLE text with hidden formulas

[dparnell]

Hello,

I have a sheet with a range of cells that have a variation of the following formula in them ( =IF(Research!V1="","",Research!V1 - of course, "V1"). Basically, the sheet is a "table of contents" of sorts, that aggregates data another sheet ("Research" sheet). (Please see the attached image).

I am trying to conditionally format the cells to be a different color if there is any text that shows up. When I do the standard "=ISTEXT" formula, it still changes the "blank" cells to another color. I assume that it is recognizing the text from the formulas. Can someone please help me by showing me how to properly format the range of cells so that, if, some text is visible (not just the formula), it will become highlighted.

Thanks so much for your expertise, in advance!

David

 

[SanjayGMusafir]

Generally, if a formula is returning some value, Excel would consider it a text. Can't be sure with returning Numbers but returning Blanks is a Value hence Text.

Better is to think around where you can use Formula like below for conditional formatting - Try this

=Find("V1",E2)>0

Assuming you need to format column E starting cell E2

 

[Fluff]

Another option would be

=LEN(D6)>0

changing cell ref to suit.

 

[dparnell]

Generally, if a formula is returning some value, Excel would consider it a text. Can't be sure with returning Numbers but returning Blanks is a Value hence Text.

Better is to think around where you can use Formula like below for conditional formatting - Try this

=Find("V1",E2)>0

Assuming you need to format column E starting cell E2

Hi Sanjay - thanks for the response. I tried this and it didn't work for me. Please see the following screencast; I think I did a poor job of explaining this with my initial chat ( Screen video of conditional format ). Please let me know what you think. Thank you!

 

[dparnell]

Another option would be

=LEN(D6)>0

changing cell ref to suit.

Hi Fluff - I tried this and it didn't work for me. I posted a screencast to Sanjay's response; please see that for a better explanation of what I am doing. Thank you very much for your help!

 

[dparnell]

Hi Sanjay - thanks for the response. I tried this and it didn't work for me. Please see the following screencast; I think I did a poor job of explaining this with my initial chat ( Screen video of conditional format ). Please let me know what you think. Thank you!

Sanjay - I found something from Fluff (in one of the "similar" threads that works = =$G1<>"" . Thanks for your help!

 

[dparnell]

Another option would be

=LEN(D6)>0

changing cell ref to suit.

Fluff - I found something from you on one of the "similar" threads that works =

=$G1<>""

. Thanks for your help!

 

[Fluff]

Glad you sorted it & thanks for the feedback.