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.

 

[Fluff]

Will you always have exactly 10 satellite events for each column?

 

[ollyhughes1982]

Yes, 10 closest, geographically

 

[Fluff]

In that case bebo's formula should work, unless the empty sells in that column contain a formula that returns ""

 

[ollyhughes1982]

In that case bebo's formula should work, unless the empty sells in that column contain a formula that returns ""

No formula as such. There is one spilled filter and choosecol formula that spills over the 10 column and 11 row range

 

[Fluff]

In that case it's a formula that returns ""

 

[ollyhughes1982]

In that case it's a formula that returns ""

Ah!

 

[Fluff]

Does the formula I provided in post#4 also highlight the cell when the column is "blank"?

 

[ollyhughes1982]

Does the formula I provided in post#4 also highlight the cell when the column is "blank"?

Apologies, have been tied up since last night. I have just tried your formula again and that does indeed cater for the blank header cell. Thanks again for your help.