Change Color of Dot, based off a cell referencing another sheet. Yes/No Scenario

J Smith

I have source she that contains a cell with a yes/no option My goal:

Yes = Green Dot

No = Red Dot

Blank = No Dot

Budget ID is the identifier

{CH Pay No} is the reference sheet

=IF({CH Pay No} = "No", "Red", MATCH(Jan11{Budget ID}))

Above is what I have tried so far plus a few variations. No dice. If I could get the "Red" to work I was going to add in the Green & Blank. Hoping someone may know the answer to this riddle. What am I missing?

Thanks

Genevieve P.

Ah yes of course! Our INDEX(MATCH needs two to close it off, that's why:

=IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0)) = "Yes", "Green", IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0)) = "No", "Red", ""))

That should do it! 🙂

Genevieve P.

Hi @J Smith

Is your {CH Pay No} reference just the one cell that you're looking to find the value of? If so, try something like this, where you COUNT to see if it's "Yes", "No", or "Blank".

=IF(COUNTIF({One Cell}, "Yes") = 1, "Green", IF(COUNTIF({One Cell}, "No") = 1, "Red", ""))

However I see you're using MATCH. Is this because you have multiple rows with different Budget IDs that you want to search for this value? If so, you'll want to use an INDEX(MATCH to bring back the "Yes" or "No" and embed that in the IF statement instead of the COUNTIFs above.

An INDEX(MATCH works like this:

INDEX({Column with value to return}, MATCH(UniqueID@row, {Column with ID to Match}, 0)

Then the IF statement Structure would be like this:

=IF(formula = "Yes", "Green", IF(formula = "No", "Red", ""))

Full Formula Example:

=IF(INDEX({CH Pay No}, MATCH(Jan@row, {Budget ID}, 0) = "Yes", "Green", IF(INDEX({CH Pay No}, MATCH(Jan@row, {Budget ID}, 0) = "No", "Red", ""))

Let me know if this works for you!

Cheers,

Genevieve

J Smith

Hi Genevieve, Thank you for looking at this with me

I feel like we are close on this one. Below is what I have in there right now. I'm getting #invalid data type error. I have moved the brackets around as well as the commas and will then get imparseable error. I started the formula with the index command and was able to pull the data from the other sheet. But getting it to replace the word "Yes" or "No" with a colored dot remains the challenge

=IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0) = "Yes"), "Green", IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0) = "No"), "Red", ""))

Genevieve P.

Hi @J Smith

My apologies! I think I had some extra parentheses in there.

Try this structure:

=IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0) = "Yes", "Green", IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0) = "No", "Red", ""))

Cheers,

Genevieve

J Smith

I tried that earlier. No go. If I take those two parentheses out I have to add 2 more on the end. The error changes to incorrect arguement set

=IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0) = "Yes", "Green", IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0) = "No", "Red", ""))))

Genevieve P.

Ah yes of course! Our INDEX(MATCH needs two to close it off, that's why:

=IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0)) = "Yes", "Green", IF(INDEX({CP PMT}, MATCH($Jan$11, {Budget ID}, 0)) = "No", "Red", ""))

That should do it! 🙂

J Smith

Success!! You solved the riddle! Thank you so much!!