• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Format for 3 criteria

T

Thomas Kuriakose

Active Member
Dear Sirs,

Kindly find attached a sheet where conditional formatting is requited in column O. The conditions are mentioned in the sheet.

Kindly let me know how to get this formatting done based on the criteria.

Thank you so much.

with regards,
thomas
 

Attachments

Hi Thomas ,

It would be nice if you could take each of the three conditions you have listed viz.

If B2 to D21 is blank and E2 to N21 does not fall in the TAR matrix: format fill cell Red

If B2 to D21 is blank and E2 to N21 falls in the TAR matrix: format fill cell Yellow

If B2 to D21 is notblank and E2 to N21 falls in the TAR matrix: format fill cell Green

and indicate which of the cells O2 : O21 will be colored in which colors.

Narayan
 
Hi Thomas ,

The rules are still not clear , since your coloring is not consistent with the description.

We have 2 situations as far as the columns B through D are concerned :

1. All 3 column cells are blank

- Rows 2 , 3 , 5 , 7 , 8 , 9 , 11 , 12 , 13 , 15 , 17 , 19 , 21

2. At least one column cell is blank

- Rows 4 , 6 , 10 , 14 , 16 , 18 , 20

We have 2 situations as far as the columns E through N are concerned :

3. There is no match for the 1s in these columns with the rows in the range T10 : T19 which have Training in them

- Rows 2 , 3 , 4 , 5 , 7 , 8 , 10 , 12 , 14 , 16 , 17 , 19 , 20

4. There is at least one match for the 1s in these columns with the rows in the range T10 : T19 , which have Training in them

- Rows 6 , 9 , 11 , 13 , 15 , 18 , 21

Thus , there will be a total of 4 combinations of these :

1 and 3 , 1 and 4 , 2 and 3 , 2 and 4

You need to indicate what is the color to be used for each of these combinations.

Narayan
 
Dear Sir,

Apologies for not being clear on the TRA matrix and other rules.

Kindly find below -

1. The TRA matrix is based on the following -

(a) I have received training on Excel, Word and Power point - B2 to D21
But I have been asked to work on SAP where I do not have any training (E2 to N21, hence I need to be in Yellow, so I can be filtered for SAP training.
(b) I have not received any training in any field, but I am working in one or more fields, I should be red, so I can be filtered for the first possible training.
(c) I have training on the required fields and am working on the trained fields, hence I should be green.
(d) If I am not in any of these conditions mentioned, then we can have orange color to identify specific training for me.

Thank you so much.

with regards,
thomas
 
Dear Sir,

The values columns B2 to D21 are training modules where if an ID has undergone any module training will be input as Done.

The values in E2 to N21 are sections in which an ID is currently working. Some of the sections are within the training modules which is shown in the TRA matrix.

So if ID 111 has not got training in any module but is working in any section from E2 to N2 then cell O2 should be RED as ID 111 has to be immediately nominated for a training.

For ID 113, he/she has undergone training in one module, but is working in a section, where he/she has no training as per TRA matrix, hence O4 will be YELLOW.

For ID 115 he/she has undergone training in two modules and is also working in a section where adequate training from the modules have been provided as per TRA matrix so O6 will be GREEN.

This is to filter out members who need immediate training and who need some inputs to carry out their work in a particular section.

If any of the above conditions are not met, we can use color ORANGE for special training.

Thanks,

with regards,
thomas
 
Hi Thomas ,

Thanks for taking the trouble to explain. From what I can understand now ,

1. Row 5 is the only row which is completely blank ; it is in its own category. According to your last post , this category should be colored ORANGE.

2. Rows 2 , 3 , 7 , 8 , 9 , 11 , 12 , 13 , 15 , 17 , 19 , 21 are blank in columns B , C and D , but have some work going on in columns E through N ; all of them fall in a second category. According to your last post , this category should be colored RED.

3. Rows 4 , 10 , 14 , 16 , 20 have undergone training , but work is being done in an area for which they have not undergone training. According to your last post , this category should be colored YELLOW.

4. Rows 6 and 18 have undergone training , and work is being done in an area for which they have undergone training. According to your last post , this category should be colored GREEN.

Can you confirm this ?

Narayan
 
Dear Sir,

Yes all the above are true.

Your are simply the best. Thanks a ton for simplifying this into steps.

Thank you so much for this solution.

Only one query, can we do this without the helper column, i don't want the formulas to be deleted by any mistake.

Thanks once again.

with regards,
thomas
 
Hi Thomas ,

I had intentionally used a helper column so that you could examine the formula and verify that it has implemented the logic correctly.

Eliminating the helper column is just a matter of using the same formula within the CF rules , instead of the reference $P2.

See the attached file.

Narayan
 

Attachments

Dear Sir,

Thank you so much.

We are truly blessed to have this forum, with so much of knowledge sharing, multiple insights and above all for remarkable respected ninjas who are outstanding.

Thanks once again.

with regard,
thomas
 
You must log in or register to reply here.
Back
Top