Posts by james40


    NBVC as always thanks I'm gonna give it a try now !


    Jim

    I forgot to mention that you have a trailing space in B19 of the "Overview" sheet. Remove that and you should see colour...


    I've uploaded the actual sheet as it doesn't seem to be working I've applied the formatting over the range you said not sure what you meant about B19 though lol

    Perhaps a formula like:


    =COUNTIF($E3:$AF3,"<="&TODAY()+30)+COUNTIFS(INDEX('Track based competency'!$E:$AF,MATCH(A3,'Track based competency'!$A:$A,0),),"<="&TODAY()+30)


    applied to column A starting at A3.


    I don't think this will work as the names in the overview sheet are in column B where as in the track and office competence sheets they are in column A the names also occupy different rows in the sheets hence why I thought Vlookup may work ? just need to format the name cells base on the format in the other 2 sheets with red / yellow / white depending on the timescales ??


    I don't think I explained properly lol


    Jim

    Perhaps you can try using COUNTIF() to count if there any number of dates less that 30+ days or 60+ days.


    e.g. for Red: =COUNTIF(E3:AF3,"<="&TODAY()+30)
    for Yellow: =COUNTIFS(E3:AF3,"<="&TODAY()+60)


    making sure to use the "Stop if True" feature in the rules and order them RED, YELLOW, WHITE


    bet there is no way to apply tat formatting over all the cells with names in ?? would have to do each cell at a time ??


    Jim

    There are 2 sheets with competencies on and one over view sheet I left Joe Bloggs on all three sheets as the example and also data protection etc..
    could the overview sheet cells with the same names in, review the dates on the other 2 sheets for the range of dates for the person with the same name ?? IE the range from the 2 sheets other sheets so by looking at the overview sheet you could identify a person who's competencies are about to expire then check the 2 other sheets to identify the competencies ??


    Could I acheive that with vlookup maybe ??


    Hope that makes more sense ??


    Jim

    Ok So I'm not even sure if this is in the right forum


    but If you take a look at the sheet you will see what I'm trying to do, I have conditional formatting in both the "office based competency" sheet and " track based competency" sheet where the dates of competence expiry change the cell colour based on how far away the date is from Today( ) etc. Joe Bloggs is the example name I have left in.


    What I would like to do is change the cell colour containing Joe Bloggs name, to either White All competencies in date, yellow all in date but some within 60 days of Today () or Red Some due to expire within 30days though some maybe in date or within 60 days ie red is the final colour.


    I would like to do this on both the Office and track competence sheet and I assume the same formula / conditional formatting would apply over the ranges E26:AF26 for the office sheet and then E27:AM27 Track based Competency sheet.


    But the main thing is to give a view of these impending renewals on the Overview sheet. where Joe Bloggs cell colour would remain white or either fill red or yellow again either based on the previous ranges or perhaps using Vlookup to check the cell colours on the other 2 sheets Cells A26 and A27..


    Sorry for the long winder post I've hit a wall applying it over a range and comparative range lol


    Any help appreciated


    James

    It would appear that I am not as stupid as I thought lol !!!


    I got it working right with the below code !




    Could the above be made anymore efficient ??


    many thanks


    Jim

    Ok So I have been putting together a sheet to ensure Overtime is fairly shared out etc.


    I've added count cells by colour for calculating day and night shifts worked, and will be adding an OT counter including types of OT done using CountIF,


    The stumbling block is setting up the control sheet / filters as the sheet will start 01/0502018 and end 01/04/2019, so I want to filter / hide the column ranges specific to each month, so if you say only need to check July shifts then you only check the July box. but I don't want it to move off the Legend Filter tab (control sheet) when you check the box, it works one way but when unchecking it it brings the Overview sheet to screen, I've attached the sheet and the code is below ! I even tried adding a second sub to ensure I ended up back on the control sheet / filter tab and still doesn't work :(


    Any help is very much appreciated, I know I'm not far off


    N

    My second formula I gave earlier should do it:


    =COUNTIFS($B$4:$FL$90,"BRW",$A$4:$FK$90,"<>")


    NBVC thanks after some jiggery pokery I've managed to get the counts to add up between start and finish times and the totals from your formula ! Seems I had a few cells misnamed !


    the issue I'm now having is with the cyclic count I'm using this formula

    Code
    =COUNTIF($B$4:$FL$90,"WHW*")-FN115

    to count all cells with WHW and numbers and the minus the total count from your formula. But based on the percentage results it isn't accurate??


    Can you see what's wrong with it ??


    I've uploaded the sheet again to clarify its counting cells that are filled green based on the txt content above WHW is the prefix followed by numbers and a letter..


    Sorry for the late reply I've not been well for a few days and forgot about this lol..


    Tanks again !!

    Hmmmm.


    Doesn't seem to work,


    The whole range is B4:FL90
    The cells containing WOW and other abbreviations are in 28 different columns. but I only want to count the cells with WOW or CHV in for example if the cell to the left isn't empty.


    as an example [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 505"]

    [tr]


    [td]

    WED

    [/td]


    [TD="colspan: 3"]28/02/2018[/TD]

    [td]

    48

    [/td]


    [/tr]


    [tr]


    [td]

    NUMBER

    [/td]


    [td]

    From

    [/td]


    [td]

    To

    [/td]


    [td]

    a.BOX

    [/td]


    [td]

    PROTECTION (Weeks)

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    5692538

    [/td]


    [td]

    0800

    [/td]


    [td]

    1600

    [/td]


    [td]

    BRW

    [/td]


    [td]

    3653.3660.

    [/td]


    [/tr]


    [tr]


    [td]

    CLA001E

    [/td]


    [td]

    1200

    [/td]


    [td]

    1230

    [/td]


    [td]

    CLA

    [/td]


    [td]

    4.6 (WEEKLY)

    [/td]


    [/tr]


    [tr]


    [td]

    CLA002E

    [/td]


    [td]

    1230

    [/td]


    [td]

    1530

    [/td]


    [td]

    CLA

    [/td]


    [td]

    3.5 (WEEKLY)

    [/td]


    [/tr]


    [tr]


    [td]

    5690996

    [/td]


    [td]

    0800

    [/td]


    [td]

    1145

    [/td]


    [td]

    CHV

    [/td]


    [td]

    7129.3148.

    [/td]


    [/tr]


    [tr]


    [td]

    5652036

    [/td]


    [td]

    1200

    [/td]


    [td]

    1500

    [/td]


    [td]

    CHV

    [/td]


    [td]

    52.37.

    [/td]


    [/tr]


    [tr]


    [td]

    5635021

    [/td]


    [td]

    2200

    [/td]


    [td][/td]


    [td]

    CHV

    [/td]


    [td]

    3136.3109.3148.

    [/td]


    [/tr]


    [tr]


    [td]

    5689058

    [/td]


    [td]

    2200

    [/td]


    [td][/td]


    [td]

    CHV

    [/td]


    [td]

    3148.3131.

    [/td]


    [/tr]


    [tr]


    [td]

    5691747

    [/td]


    [td]

    2300

    [/td]


    [td][/td]


    [td]

    CHV

    [/td]


    [td]

    44

    [/td]


    [/tr]


    [tr]


    [td]

    5635017

    [/td]


    [td][/td]


    [td]

    0600

    [/td]


    [td]

    CHV

    [/td]


    [td]

    3136.3109.3148.

    [/td]


    [/tr]


    [tr]


    [td]

    5689057

    [/td]


    [td][/td]


    [td]

    0600

    [/td]


    [td]

    CHV

    [/td]


    [td]

    3148.3131.

    [/td]


    [/tr]


    [tr]


    [td]

    5691910

    [/td]


    [td][/td]


    [td]

    0600

    [/td]


    [td]

    CHV

    [/td]


    [td]

    3184.3175.

    [/td]


    [/tr]


    [tr]


    [td]

    5687606

    [/td]


    [td]

    0900

    [/td]


    [td]

    1500

    [/td]


    [td]

    KNW

    [/td]


    [td]

    63

    [/td]


    [/tr]


    [tr]


    [td]

    5686331

    [/td]


    [td]

    0105

    [/td]


    [td]

    0530

    [/td]


    [td]

    MEN

    [/td]


    [td]

    352.165.163.184.

    [/td]


    [/tr]


    [tr]


    [td]

    5686332

    [/td]


    [td]

    0110

    [/td]


    [td]

    0510

    [/td]


    [td]

    MEN

    [/td]


    [td]

    190.2029.187.192.

    [/td]


    [/tr]


    [tr]


    [td]

    5686817

    [/td]


    [td]

    0115

    [/td]


    [td]

    0445

    [/td]


    [td]

    MEN

    [/td]


    [td]

    376

    [/td]


    [/tr]


    [tr]


    [td]

    MEN002E

    [/td]


    [td]

    0815

    [/td]


    [td]

    0855

    [/td]


    [td]

    MEN

    [/td]


    [td]

    1787.9017 (EVERY WEEK)

    [/td]


    [/tr]


    [tr]


    [td]

    MEN001E

    [/td]


    [td]

    0830

    [/td]


    [td]

    1130

    [/td]


    [td]

    MEN

    [/td]


    [td]

    1787 (WEEKLY)

    [/td]


    [/tr]


    [tr]


    [td]

    5688982

    [/td]


    [td]

    1130

    [/td]


    [td]

    1600

    [/td]


    [td]

    MEN

    [/td]


    [td]

    386.181.

    [/td]


    [/tr]


    [tr]


    [td]

    5686064

    [/td]


    [td]

    1135

    [/td]


    [td]

    1600

    [/td]


    [td]

    MEN

    [/td]


    [td]

    1787.3.5.9017.

    [/td]


    [/tr]


    [tr]


    [td]

    5692292

    [/td]


    [td]

    1800

    [/td]


    [td]

    2200

    [/td]


    [td]

    MEN

    [/td]


    [td]

    1210.2035.

    [/td]


    [/tr]


    [tr]


    [td]

    5692293

    [/td]


    [td]

    1800

    [/td]


    [td]

    2200

    [/td]


    [td]

    MEN

    [/td]


    [td]

    1207.2036.

    [/td]


    [/tr]


    [tr]


    [td]

    5678338

    [/td]


    [td]

    0800

    [/td]


    [td]

    1200

    [/td]


    [td]

    MES

    [/td]


    [td]

    154

    [/td]


    [/tr]


    [tr]


    [td]

    5689415

    [/td]


    [td]

    1000

    [/td]


    [td]

    1010

    [/td]


    [td]

    MES

    [/td]


    [td]

    345

    [/td]


    [/tr]


    [tr]


    [td]

    MES002E

    [/td]


    [td]

    1130

    [/td]


    [td]

    1150

    [/td]


    [td]

    MES

    [/td]


    [td]

    152 (EVERY WEEK)

    [/td]


    [/tr]


    [tr]


    [td]

    5689416

    [/td]


    [td]

    1200

    [/td]


    [td]

    1210

    [/td]


    [td]

    MES

    [/td]


    [td]

    345

    [/td]


    [/tr]


    [tr]


    [td]

    MES001E

    [/td]


    [td]

    1330

    [/td]


    [td]

    1340

    [/td]


    [td]

    MES

    [/td]


    [td]

    139 (EVERY WEEK)

    [/td]


    [/tr]


    [tr]


    [td]

    5688423

    [/td]


    [td]

    2345

    [/td]


    [td][/td]


    [td]

    MES

    [/td]


    [td]

    16.11.

    [/td]


    [/tr]


    [tr]


    [td]

    5688421

    [/td]


    [td][/td]


    [td]

    0545

    [/td]


    [td]

    MES

    [/td]


    [td]

    16.11.

    [/td]


    [/tr]


    [tr]


    [td]

    5687741

    [/td]


    [td]

    1015

    [/td]


    [td]

    1025

    [/td]


    [td]

    NWW

    [/td]


    [td]

    428

    [/td]


    [/tr]


    [tr]


    [td]

    5687743

    [/td]


    [td]

    1015

    [/td]


    [td]

    1025

    [/td]


    [td]

    NWW

    [/td]


    [td]

    3495

    [/td]


    [/tr]


    [tr]


    [td]

    5684894

    [/td]


    [td]

    2330

    [/td]


    [td][/td]


    [td]

    NWW

    [/td]


    [td]

    431

    [/td]


    [/tr]


    [tr]


    [td]

    5684775

    [/td]


    [td][/td]


    [td]

    0600

    [/td]


    [td]

    NWW

    [/td]


    [td]

    3485.3486.

    [/td]


    [/tr]


    [tr]


    [td]

    5678199

    [/td]


    [td]

    0010

    [/td]


    [td]

    0455

    [/td]


    [td]

    SHW

    [/td]


    [td]

    217.5267.7325,

    [/td]


    [/tr]


    [tr]


    [td]

    5689078

    [/td]


    [td]

    0800

    [/td]


    [td]

    1530

    [/td]


    [td]

    STW

    [/td]


    [td]

    87

    [/td]


    [/tr]


    [tr]


    [td]

    5688767

    [/td]


    [td]

    0900

    [/td]


    [td]

    1700

    [/td]


    [td]

    STW

    [/td]


    [td]

    5837

    [/td]


    [/tr]


    [tr]


    [td]

    5690224

    [/td]


    [td]

    0930

    [/td]


    [td]

    0945

    [/td]


    [td]

    WOW

    [/td]


    [td]

    5433

    [/td]


    [/tr]


    [tr]


    [td]

    5693659

    [/td]


    [td]

    2300

    [/td]


    [td][/td]


    [td]

    CHV

    [/td]


    [td]

    3168.3165.

    [/td]


    [/tr]


    [tr]


    [td]

    5693794

    [/td]


    [td][/td]


    [td]

    0600

    [/td]


    [td]

    CHV

    [/td]


    [td]

    3148.3131.

    [/td]


    [/tr]


    [tr]


    [td]

    5693810

    [/td]


    [td]

    2200

    [/td]


    [td][/td]


    [td]

    CHV

    [/td]


    [td]

    3148.3131.

    [/td]


    [/tr]


    [/TABLE]


    If counting the above "CHV" the correct total is 6 as I forgot to sort the table , I didn't realise I could post the table like that lol .. hopefully it better explains what I mean ?


    Thanks for the help it is appreciated !!


    Jim

    Hi and thanks for any help it is truly appreciated !


    Ok so I am currently using a formula to count the number of cells in a range as below:


    Code
    =COUNTIF($B$4:$FL$90,"WOW")


    Also

    Code
    =COUNTIF($B$4:$FL$90,"WOW*")-FM108


    Now my issue if that some cells with WOW as the value have an adjacent cell to the immediate left which is where the finish time would normally go and I would like to exclude these from the count, as they are counted in a different way.


    I've searched around and found some SUMIF examples but I'm struggling to get this to work !


    I know the over all count is ok as there is a discrepancy of 21 in 2 separate parts of the table which means 21 night shifts have been counted twice !


    Again any help is truly appreciated I'm absolutely shattered and probably overlooking something simple but for the life of me I cant work it out and likely to headbut the keyboard shortly LOL


    James