Conditional format based on other cell colours / values

[ollyhughes1982]

Hi,

I have the below conditional formatting formula, which turns cells green if I have completed the parkrun named in each box:

=INDEX('Completed Unique E(V)'!$B$4:$B$2003,MATCH(C180,'Completed Unique E(V)'!$B$4:$B$2003,0))<>""

This works great and no issues. Now, what I want to do is turn the header event (venue) [cells C179:L179] cells green, if all of the subsequent events below them have been completed.

e.g. If i completed Sutton Park parkrun [cell C186] and thus turned it green, I would then want the header event (venue) [cell C179] to also turn green; as all satellite events (venues) below it would then have been completed.

I’m not quite sure how to do this - Do I need some sort of conditional formatting formula that looks for cells coloured green?

Thanks in advance!

Olly.

 

[bebo021999]

When all cells in the range C180:C189 turn green, it indicates that each cell in this range is found within the range 'Completed Unique E(V)'!$B$4:$B$2003.
Therefore, you can use the COUNTIF function to check, as follows:

COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0

Next, count the results that equal zero:

=SUMPRODUCT(--(COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0))

And if the result is zero, it means all cells in C180 are found:

=SUMPRODUCT(--(COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0)) = 0

Place this in the Conditional Formatting for cell C179, then apply it to the entire row 179.

P.S.: The CF formulas you currently have for the inner cells use the INDEX function. However, another way is to use COUNTIF > 0 to check for the existence of a cell within the range, for example:

=COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C180) > 0

This is sufficient.

 

[ollyhughes1982]

When all cells in the range C180:C189 turn green, it indicates that each cell in this range is found within the range 'Completed Unique E(V)'!$B$4:$B$2003.
Therefore, you can use the COUNTIF function to check, as follows:

COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0

Next, count the results that equal zero:

=SUMPRODUCT(--(COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0))

And if the result is zero, it means all cells in C180 are found:

=SUMPRODUCT(--(COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0)) = 0

Place this in the Conditional Formatting for cell C179, then apply it to the entire row 179.

P.S.: The CF formulas you currently have for the inner cells use the INDEX function. However, another way is to use COUNTIF > 0 to check for the existence of a cell within the range, for example:

=COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C180) > 0

This is sufficient.

Great, I've popped out, but when I get back I will give this a try. Thanks

 

[Fluff]

Another option

=SUM(COUNTIFS('Completed Unique E(V)'!$B$4:$B$2003,C$180:C$250))=COUNTA(C$180:C$250)

 

[ollyhughes1982]

When all cells in the range C180:C189 turn green, it indicates that each cell in this range is found within the range 'Completed Unique E(V)'!$B$4:$B$2003.
Therefore, you can use the COUNTIF function to check, as follows:

COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0

Next, count the results that equal zero:

=SUMPRODUCT(--(COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0))

And if the result is zero, it means all cells in C180 are found:

=SUMPRODUCT(--(COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0)) = 0

Place this in the Conditional Formatting for cell C179, then apply it to the entire row 179.

P.S.: The CF formulas you currently have for the inner cells use the INDEX function. However, another way is to use COUNTIF > 0 to check for the existence of a cell within the range, for example:

=COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C180) > 0

This is sufficient.

This worked perfectly. Thanks

 

[ollyhughes1982]

Another option

=SUM(COUNTIFS('Completed Unique E(V)'!$B$4:$B$2003,C$180:C$250))=COUNTA(C$180:C$250)

This also worked, thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ollyhughes1982]

Apologies, one small thing. Would it be possible to add something into the conditional formula that would keep the cell unfilled when there is no entry in the field? I have shown an example in the image, below:

It just looks a bit strange with the header being green whilst the component parts are empty.

Thanks again.

 

[Fluff]

How about

=AND(SUM(COUNTIFS('Completed Unique E(V)'!$B$4:$B$2003,C180:C250))=COUNTA(C180:C250),COUNTA(C180:C250)>0)

 

[ollyhughes1982]

Thanks. I ended up using this formula, from bebo:

=SUMPRODUCT(--(COUNTIF('Completed Unique E(V)'!$B$4:$B$2003, C$180:C$189) = 0)) = 0

Is there any way I could add the functionality into that, to save me re-doing all my conditional formats with this formula, please? Or would I need to swap to that one? Thanks again