Need assistance with a formula for INDIRECT color/highlight with text terms.

**whatthekeck31** · 02-20-2013, 08:24 PM

Ok...so I've been trying to make this spreadsheet for work (for all the employees) to kind of...optimize our work flow.

Here is the formula I'm having trouble with (and it will be the same for the rest of them). These are the codes I'm using for each individual highlight/color

=INDIRECT("F"&ROW())="Too Far Away"
Same for all but with words: "Interested" "Not Interested" "Submitted"....etc.

I'm attaching the file...

I want to keep the rows colored from the formula I have, but, whenever I put: Sourced, Called, Emailed, Spoke To....the row won't stay colored.

I'm just wondering how I can do this, thanks.

I'm guessing it's going to be a COUNTIF formula, but I'm not sure.....

I need to be able to use the: Sourced, Called, Emailed, Spoke To (No coloring) along with the "color" terms. AND they also have to be able to count in the TOTAL. Thanks everyone

Mail Merge Organizer 3.0.xlsx

Ok, so here's an example:

row 1: interested (OK: highlighted AND added to total)
row 2: not interested (OK: highlighted AND added to total)
row 3: sourced, called (OK: added to total and no color)
row 4: sourced, not interested(BAD: Not highlighted in the "not interested" color and "not interested" was not added to a total)
EXAMPLE: example.xlsx example.xlsx

**mellowmarshall** · 02-20-2013, 09:27 PM

could you add some example to your spreadsheet in the status column? im not sure i understand what the problem is

**whatthekeck31** · 02-20-2013, 09:37 PM

In the status column...like, ok, so

this works: interested

It will change the whole row to green, which is perfect.

However, if I do: sourced, interested
It won't turn green....

I do not want these words to change any colors: sourced, emailed, called, spoke to.

but i DO want them to be changed if they are paired with any of the words from the colored options....

**whatthekeck31** · 02-20-2013, 09:46 PM

Ok, so here's an example:

row 1: interested (OK: highlighted AND added to total)
row 2: not interested (OK: highlighted AND added to total)
row 3: sourced, called (OK: added to total and no color)
row 4: sourced, not interested(BAD: Not highlighted in the "not interested" color and "not interested" was not added to a total)

here's the attached example.

example.xlsx

**whatthekeck31** · 02-21-2013, 12:06 AM

Please, I really need some help on this. I feel like I'm just missing on a simple formula or something.

**mellowmarshall** · 02-21-2013, 12:16 AM

beyond me too, i think. the conditional formatting evaluation doesn't like wildcards and it won't accept a lot of functions i've tried.

**whatthekeck31** · 02-21-2013, 12:21 AM

I'm thinking it has to be an OR or...maybe a COUNTIF or IF formula PLUS the INDIRECT one I have....

**vlady** · 02-21-2013, 01:36 AM

maybe like this

=COUNTIF(E:E,"*Not Interested*")

and CF like this

=SEARCH("*Not Interested*",INDIRECT("E"&ROW()))=1

**whatthekeck31** · 02-21-2013, 07:48 AM

Are those two separate formulas/rules? If they aren't, how can I combine them into one?

**whatthekeck31** · 02-21-2013, 07:50 AM

Also, are those formulas for the cell itself, or is it for the rule I'll be making? Thanks for the help.

**mellowmarshall** · 02-21-2013, 08:21 AM

I think that would be #1 for the count cells, and #2 in the CF. Also, if you haven't already you can use =countif("interested") - (cell with "not interested). I noticed if you use wildcards before and after those countif funcs it will match "interested" from each cell that says "not interested".

**whatthekeck31** · 02-21-2013, 08:52 AM

Well, I already have the COUNTIF in the cell under TOTAL for it...

I guess the big thing is trying to get something like "not interested" into a cell with something like "sourced" and have it colored based off of the "not interested" CF....

**whatthekeck31** · 02-21-2013, 02:12 PM

What about if I wanted "not interested AND sourced". How can I make other words not uncolor the cells?

**whatthekeck31** · 02-21-2013, 03:20 PM

Ok, so far I'm solid. Now my problem is both "interested" and "not interested" add up in the TOTAL column. Like "interested" also counts into not interested TOTAL and vice versa. Is there a way to make it a specific term? So..."not interested" has no association with "interested"??? Thanks.

Need assistance with a formula for INDIRECT color/highlight with text terms.

LinkBack

Thread Tools

Rate This Thread

Display

Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Formula help

Re: Formula help

Re: Formula help

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Re: Need assistance with a formula for INDIRECT color/highlight with text terms.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions