Conditional formatting

[ktexcel]

Hi

I’m working on a workbook and need some help with what I think will be conditional formatting.

I have 7 worksheets, 6 different themes and an overview sheet. Each of the themes has a series of checkboxes relating to challenges, the challenges are split into two groups. What I’m trying to achieve is that when each person gains a checkbox within each of the different groups on a given theme, the relevant theme cell in the overview sheet will be highlighted to notify their eligibility for an award.

How do I do this please?

Thanks
Katie

 

[etaf]

yep, sounds like condtional formatting

but without a sample not sure how - checkboxes are true/false so within a theme you can count those - so if the countif() is > what ever number to qualify - you can change fill colour

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

 

[ktexcel]

Hi

Thank you, please see attached sample. Within the 'overview' sheet you will see coloured cells which is what I would like to CF to do. these will be drawn from the themes. I have put examples in the 'know yourself' tab, the different shades represent the two groups I of challenges I referred to. At least 1 checkbox will need to be completed in each group. Where there is 2 or more in one group and none in the other group the CF would not apply (example rows 10,11,12 and 14).

Thanks

 

[etaf]

you have a name in overview - so 1 row = 1 person
you have a name in each them so 1 row = 1 person

in C to P on know my self - 2 sections , same on all the sheets 2 sections

so what are we counting

up to row 15 - Skills builders
row 12 and 14 no tick

on the next section row 10 & 11 dont have a tick

so because any section has a missing row - then that counts on the overview

ok, i think i have it - just work, top of head maybe an OR with 2 countifs()

 

[ktexcel]

The names will carry from overview to each sheet, so row 3 will belong to Bob Jones and that will be their progress.

They will need a checkbox in the section C to H and a checkbox in the section I to P.

Thanks

 

[etaf]

this is a bit messy
=AND(SUMPRODUCT(--FILTER('Know myself'!$C$3:$H$200,('Know myself'!$A$3:$A$200=$A3))), SUMPRODUCT(--FILTER('Know myself'!$I$3:$P$200,('Know myself'!$A$3:$A$200=$A3))))

but will do the required conditional formatting

however it would be good to probably use the sheet name from the title - but that will be lot of indirect and not good as volatile and may slow spreadsheet

I'm sure this could be simplified in 365 - maybe with a LET or other function , need to think about that

 

[etaf]

maybe this will do it all
=IFERROR(AND(SUMPRODUCT(--FILTER(INDIRECT("'"&C$2&"'!$C$3:$H$200"),INDIRECT("'"&C$2&"'!$A$3:$A$200")=$A3)),SUMPRODUCT(--FILTER(INDIRECT("'"&C$2&"'!$I$3:$P$200"),INDIRECT("'"&C$2&"'!$A$3:$A$200")=$A3))),FALSE)
uses the sheet name from row 2
make 100% sure the row 2 = sheet name which i think it does

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
C3:H200 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:

=IFERROR(AND(SUMPRODUCT(--FILTER(INDIRECT("'"&C$2&"'!$C$3:$H$200"),INDIRECT("'"&C$2&"'!$A$3:$A$200")=$A3)),SUMPRODUCT(--FILTER(INDIRECT("'"&C$2&"'!$I$3:$P$200"),INDIRECT("'"&C$2&"'!$A$3:$A$200")=$A3))),FALSE)

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

maybe consider a helper column may help here to use a countif -=

 

[jayjay1961]

CF formula: =AND(COUNTIF('Know myself'!C3:H3,TRUE)=1,COUNTIF('Know myself'!I33,TRUE)=1)