Percentage calculation based on color in a training matrix

Hii

Red & Green color i have given conditional formatting,red for expiry dates & green 30 days prior expiry..
RMS & helper also should be consider in conditional formatting if mandatory courses missing.. and i have more primary roles as well..

Hope you my point..

HI,

I have used supporting column to achieve the desired results if u r comfort with this , i shall ask few more question to complete the rest of the part

Note:
I have converted the Table to range for my convenience

Punnam

Yes punnam go ahead...

(IND,H2S,IR,DR,RB),you can assign this courses as mandatory for Helpers,IND,H2S,IR,DR,RB,CAM,HMS,CO for RMS..punnam that
was a sample workbook,just show me how it works,i'll do the modifications as per my req..

Thanks..

If condition-1 in supporting column like column 1 to 10 .one if condition-2 and from 11 to etc. U can still go for nested if condition in the supporting column which are on wright side of u r table ,and update the Conditional format "=>1", Color or leave it.

Punnam

didnt get your point,could you please give a sample..

Punnam I didnt get your point,How it works and how i will get my desired results..

Originally Posted by Punnam

If condition-1 in supporting column like column 1 to 10 .one if condition-2 and from 11 to etc. U can still go for nested if condition in the supporting column which are on wright side of u r table ,and update the Conditional format "=>1", Color or leave it.

Punnam

hi Akbar,
1) =IF(AND(IF(OR(book1!$C9="Driver",book1!$C9="Supervisor"),D9="")),1,"") the formula in Column "Q" , will check for the name "Driver"&"Supervisor" in the column D , and if the D9 is ="" and results you value 1,
2) The second part is the condition which i have used in conditional format if the the cell value in Column Q is 1 then color the cell or no change

Please check the Column Q and u will get a idea
Sorry for the late reply .

Punnam

Thanks for the solution,but i'm looking for some thing more stable..

hi Akbar
i think is it is stable with a helper columns
Can u explain me in detail what is the difficulty u r facing in this so that will try to minimize it or we shall ask for more help .
Punnam

Hi Punnam,

How could i get my desired results,i mean the percentage..moreover i have columns (courses)of about 50,so i don't think it is
practible to copy the formula's to the required columns of the sub table that you have created..

I was looking for a macro which could generate "Required" or "Cell Color Change" for the mandatory courses,if the field is empty..

Thanks,
Akbar

akbar
I used a Vlookup & Match function , i tabulated u r mandatory fields , and the same is working as per u r requirement .int this process i removed the rest conditions which u need to update again.
1) Under stand check the vlookup i have used in conditional formatting tab for the logic.
Find the updated File
Punnam

Thanks Punnam,this is better..and the 2nd part,how to get the percentage..

Hi akbar Explain me in more detail .
Provide required results manually for better understanding
Punnam

Hi Punnam,

Drivers have Total 6 Mandatory Courses,Supervisor - 8,RMS-8,Helper - 5,
so the total courses is total driver nos multiplied by their mandatory courses (5 Drivers x 6),(3 RMS x 8),(4 Helper x 5) like
this you will get the total courses,for the percentage it should subtract from expired or required ..

Hope you got the point..

hi Check this file updated wit macros which will match u r requirement .

The only issue with the macros is the function is need to need to re do after every change .


Punnam

Thanks Punnam Will check it..