Formatting if cell value found in range

M

MeaclH

Board Regular
Joined
Apr 2, 2014
Messages
109
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Gday all,

I am looking for a formula that will apply conditonal formatting to a cell if the value in that cell is found elsewhere in a range.
In the sample data below for example, in rows 35,38,41,44 and so on, I would like to see if there are duplicates of any values. The one privso I can't seem to figure out is that I only want it to show duplicates for certain values.
This is a roster so there may be multiple people rostered OFF which I don't want to change the formatting of, only the shifts. I'm happy to use the codes of all 20 odd shifts in the formula if that would make it easier?

Hope that makes sense.
Cheers,
Hayden

DIGITAL ROSTER - VERSION 3.0.xlsm
N
35OFF
36
37
380530NCC
39
40
412130PL/S
42
43
441330NCC
45
46
47OFF
48
49
500530PB
51
52
53OFF
54
55
562130PB
57
58
59OFF
60
61
621330PB
63
64
65OFF
66
67
680530PL/S
69
70
71OFF
72
73
74OFF
75
76
771330BC
78
79
802130NCC
81
82
831330PL/S
84
85
86OFF
87
88
892130BC
90
91
920530BC
93
94
FN 23MAR-05APR25
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "SPDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "CPDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "BONUS"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "MATLVE"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "PATLVE"textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valueending with "?"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "GAZETTE"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "AV"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "EDO-U"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "OFF-U"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "BLV"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "CTFN"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "CDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "STFN"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "SDO"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "LSL"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "A/L"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "PHC"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "OFF"textYES
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "EDO"textYES
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COLUMN()=CELL("COL")textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=G$3=TODAY()textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "OK"textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "DEC"textNO
 
Sort by date Sort by votes
kevin9999 said:
I'm still not clear on exactly what you want. If I interpret your requirement of formatting cells in the range C25:C92 where the code is a duplicate but not "OFF", then is this what you want:

Book1
C
25OFF
26
27
280530NCC
29
30
312130PL/S
32
33
341330NCC
351330NCC
36
37OFF
38
39
400530PB
41
42
43OFF
44
45
462130PB
47
48
49OFF
50
51
521330PB
53
54
55OFF
56
57
580530PL/S
590530PL/S
60
61OFF
62
63
64OFF
65
66
671330BC
68
69
702130NCC
71
720530PL/S
731330PL/S
74
75
76OFF
77
78
792130BC
80
81
820530BC
83
84
85
86
87
88
89
90
91
92
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C25:C92Expression=AND(C25<>"OFF",COUNTIF($C$25:$C$92,C25)>1)textYES
Click to expand...
Hi everyone,

Looking at Kevin's formula, I thought about a little something that could take into account multiple unwanted values (like OFF), and I would apply the conditionnal formating to the whole column C:C with this formula:

- English version -
Excel Formula: 
=AND(SUM(--MAP({"OFF", "SOMETHING ELSE", "Another unwanted value"},LAMBDA(unwanted,$C1=unwanted)))=0,COUNTIF($C:$C,$C1)>1)

-French version-
Excel Formula: 
=ET(SOMME(--MAP({"OFF";"SOMETHING ELSE";"Another unwanted value"};LAMBDA(unwanted;$C1=unwanted)))=0;NB.SI($C:$C;$C1)>1)
To simplify this, the MAP function will forward one by one the values to the LAMBDA as unwanted argument that will return if it is equal or not. The -- will change True and False to 1 and 0, and if the SUM of all this is 0 (C1 is different from everything in the list) and the value is found more than once in the C:C column then it will color the cell.

Bests regards,

Vincent
 
Last edited:
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
kevin9999 said:
Could you list them on a table on the same sheet? I think the CF formula could be made a lot simpler if you could.
Click to expand...
Hi Kevin,
Thanks for your ongoing help. Table below of values to be omitted from formula.

Cheers,
Hayden
OFF
OFF-U
EDO
X0930
TRAINING
ADHOC
FRCE
SGS
MTP
AV
SDO
STFN
SPDO
CDO
CTFN
CPDO
PHC
A/L
LSL
BVL
GAZETTE
PATLVE
MATLVE
BONUS
 
Upvote 0
zero269 said:
Hi @MeaclH

I see you're in good hands with Kevin taking care of your request regarding CFRs to address duplicates with custom exclusions.

I was looking at your request earlier and noticed you have a lot of CFR's; many using identical formatting.
I use CFR's alot in some cases, and wanted to share some advice on how to combine some of them... if interested.

For example, you have some Orange Fill being applied to the same ranges that contain either of the following text: BLV, CDO, SDO, CPDO, SPDO, CTFN, STFN
All of these could be combined using the COUNTIF function. I've used this many times in several workbooks.

Below I'm providing both a Long Version and a Short Version for the following: Orange Fill, Green Fille, Yellow Fill and No Formatting.
You can view and grab the formulas from the Mini Sheets below...

Note: I'm using wildcards (*) becasue that's what "contains" does by default.
CONSIDER: If you use...
  • PDO instead of CPDO & SPDO, will this wind up formatting anything else that shouldn't be? If not, then I would use the substitute.
  • TFN instead of CTFN & STFN...?
Criteria used for both versions:
  • Orange Fill (Long Version) using: BLV, CDO, SDO, CPDO, SPDO, CTFN, STFN
  • Orange Fill (Short Version) using: BLV, CDO, SDO, ~PDO,~TFN
  • Green Fill (Long Version) using: EDO, EDO-U
  • Green Fill (Short Version) using just EDO considering it will format both criteria anyway.
  • Yellow Fill (Long Version) using: OFF, OFF-U
  • Yellow Fill (Short Version) using just OFF considering it will format both criteria anyway.
  • No Formatting (Long Version) using: A/L, BONUS, GAZETTE, LSL, MATLVE, PATLVE, PHC
  • No Formatting (Short Version) using: nothing :)
Personally, I don't think you need to use the No Formatting CFRs. They are not being used to apply any conditional formatting and appear to have no effect on the cells that contain those various string values. Is there some other formatting taking place where these are removing formatting? Just curious.

Regarding Ranges the formatting is being applied to:

You technically have four different ranges that your formatting is being applied to for the CFR's I've mentioned above.
I also noticed you have a break in your top range (image). Those marching ants show 4 seperate selections... thus leaving G21 out of the range.

View attachment 121771
Your current range:
Excel Formula: 
=$G$35:$T$94,$G$96:$T$110,$G$112:$T$126,$G$22:$T$33,$G$19:$T19,$G$20,$H$20:$T$21
The fixed range:
Excel Formula: 
=$G$19:$T$33,$G$35:$T$94,$G$96:$T$110,$G$112:$T$126

To test the CFR's I'm proposing, I created some sample data using a list of your criteria, and applying it to your top range randomly using the following formula.
W19:W36 is a list of your criteria.
Excel Formula: 
=INDEX($W$19:$W$36,RANDBETWEEN(1,ROWS($W$19:$W$36)),1)

This 1st Example is using all of the Long Versions of the CFRs:
BLV, CDO, SDO, CPDO, SPDO, CTFN, STFN | EDO, EDO-U | OFF, OFF-U | A/L, BONUS, GAZETTE, LSL, MATLVE, PATLVE, PHC

VBA Testing.xlsm
GHIJKLMNOPQRST
19OFF-ULSLSTFNBLVBLVOFF-UCPDOSPDOA/LOFF-UOFFOFF-USPDOPATLVE
20SPDOBLVOFFEDO-USDOBLVA/LBLVOFF-UOFFEDO-USTFNEDO-USPDO
21OFF-UA/LBLVCTFNEDOSPDOEDOMATLVEBLVBLVBONUSPHCA/LEDO
22CDOCDOA/LCTFNLSLBONUSA/LCTFNLSLOFF-UMATLVEOFF-USPDOSTFN
23BONUSCPDOEDOBLVBONUSLSLCTFNOFFLSLPATLVEEDOSPDOCDOEDO
24CDOPATLVEMATLVESPDOPHCSPDOEDOA/LEDO-UOFF-USDOCPDOEDO-ULSL
25PHCMATLVECPDOCDOLSLCPDOCPDOLSLCPDOBLVSPDOBONUSOFFPHC
26PATLVEOFFA/LCTFNSTFNBLVOFFBONUSMATLVEMATLVEBLVA/LCDOA/L
27STFNEDO-UCPDOOFF-UCTFNMATLVEOFF-ULSLEDO-UEDO-UPATLVEOFFPHCOFF-U
28SDOCTFNSTFNA/LGAZETTEPATLVEOFF-USTFNGAZETTEMATLVECTFNCDOEDOCTFN
29SPDOEDOCTFNGAZETTECPDOEDO-USDOPATLVECDOGAZETTECPDOA/LMATLVEOFF-U
30GAZETTESTFNSPDOCTFNCTFNCPDOGAZETTEPHCPHCLSLPATLVEBONUSEDO-UA/L
31EDOMATLVEBONUSPHCMATLVEBLVCPDOBONUSSDOBONUSOFFSTFNEDO-UCDO
32BLVLSLPATLVECDOCPDOLSLGAZETTEBLVLSLLSLSTFNSDOLSLLSL
33EDO-UGAZETTECTFNCPDOGAZETTECTFNMATLVESTFNCTFNCPDOSPDOLSLMATLVESTFN
CountUnique
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19:T33,G35:T94,G96:T110,G112:T126Expression=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))textNO
G19:T33,G35:T94,G96:T110,G112:T126Expression=OR(COUNTIF(G19,"*EDO*"), COUNTIF(G19,"*EDO-U*"))textNO
G19:T33,G35:T94,G96:T110,G112:T126Expression=OR(COUNTIF(G19,"*OFF*"), COUNTIF(G19,"*OFF-U*"))textNO
G19:T33,G35:T94,G96:T110,G112:T126Expression=OR(COUNTIF(G19,"*A/L*"), COUNTIF(G19,"*BONUS*"), COUNTIF(G19,"*GAZETTE*"), COUNTIF(G19,"*LSL*"), COUNTIF(G19,"*MATLVE*"), COUNTIF(G19,"*PATLVE*"), COUNTIF(G19,"*PHC*"))textNO


This 2nd Example is using all of the Short Versions of the CFRs:
BLV, CDO, SDO, PDO, TFN | EDO | OFF | nothing

VBA Testing.xlsm
GHIJKLMNOPQRST
19OFF-ULSLSTFNBLVBLVOFF-UCPDOSPDOA/LOFF-UOFFOFF-USPDOPATLVE
20SPDOBLVOFFEDO-USDOBLVA/LBLVOFF-UOFFEDO-USTFNEDO-USPDO
21OFF-UA/LBLVCTFNEDOSPDOEDOMATLVEBLVBLVBONUSPHCA/LEDO
22CDOCDOA/LCTFNLSLBONUSA/LCTFNLSLOFF-UMATLVEOFF-USPDOSTFN
23BONUSCPDOEDOBLVBONUSLSLCTFNOFFLSLPATLVEEDOSPDOCDOEDO
24CDOPATLVEMATLVESPDOPHCSPDOEDOA/LEDO-UOFF-USDOCPDOEDO-ULSL
25PHCMATLVECPDOCDOLSLCPDOCPDOLSLCPDOBLVSPDOBONUSOFFPHC
26PATLVEOFFA/LCTFNSTFNBLVOFFBONUSMATLVEMATLVEBLVA/LCDOA/L
27STFNEDO-UCPDOOFF-UCTFNMATLVEOFF-ULSLEDO-UEDO-UPATLVEOFFPHCOFF-U
28SDOCTFNSTFNA/LGAZETTEPATLVEOFF-USTFNGAZETTEMATLVECTFNCDOEDOCTFN
29SPDOEDOCTFNGAZETTECPDOEDO-USDOPATLVECDOGAZETTECPDOA/LMATLVEOFF-U
30GAZETTESTFNSPDOCTFNCTFNCPDOGAZETTEPHCPHCLSLPATLVEBONUSEDO-UA/L
31EDOMATLVEBONUSPHCMATLVEBLVCPDOBONUSSDOBONUSOFFSTFNEDO-UCDO
32BLVLSLPATLVECDOCPDOLSLGAZETTEBLVLSLLSLSTFNSDOLSLLSL
33EDO-UGAZETTECTFNCPDOGAZETTECTFNMATLVESTFNCTFNCPDOSPDOLSLMATLVESTFN
CountUnique
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19:T33,G35:T94,G96:T110,G112:T126Expression=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))textNO
G19:T33,G35:T94,G96:T110,G112:T126Expression=COUNTIF(G19,"*EDO*")textNO
G19:T33,G35:T94,G96:T110,G112:T126Expression=COUNTIF(G19,"*OFF*")textNO
Click to expand...
Mate!
This is such a classy touch to offer these suggestions! I had a feeling there was probably a more efficient way of setting the CF up but figuring it out fell by the way side.

Thank you very much for offering up your expertise and taking the time to detail it so well.

I have implemented the changes and everything is working perfectly. Makes for a much tidier sheet and mini sheet when I need to share it.

Love ya work!
Hayden
 
Upvote 0
One way is to have a list (somewhere) of all the exceptions to the CF rule, then reference it in your CF formula. Something like the following:

CF.xlsm
CDE
25OFFExceptions
260530NCCOFF
27OFF-U
280530NCCEDO
29ADHOCX0930
30TRAINING
312130PL/SADHOC
322130PL/SFRCE
33SGSSGS
341330NCCMTP
351330NCCAV
36SDOSDO
37OFFSTFN
38SDOSPDO
39CDOCDO
400530PBCTFN
41SDOCPDO
42CDOPHC
43OFFA/L
44SDOLSL
45BVL
462130PBGAZETTE
472130PBPATLVE
48AVMATLVE
49OFFBONUS
50
51AV
521330PB
53
54AV
55OFF
56
57
580530PL/S
590530PL/S
60
61OFF
622130PB
632130PB
64OFF
65
66
671330BC
68
69
702130NCC
71
720530PL/S
731330PL/S
74
75
76OFF
77
78
792130BC
80
81
820530BC
83ADHOC
84FRCE
85SGS
86MTP
87ADHOC
88FRCE
89SGS
90MTP
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C25:C92Expression=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF($C$25:$C$92,C25)>1)textYES
 
Upvote 0
MeaclH said:
Hi Kevin,
Thanks for your ongoing help. Table below of values to be omitted from formula.

Cheers,
Hayden
OFF
OFF-U
EDO
X0930
TRAINING
ADHOC
FRCE
SGS
MTP
AV
SDO
STFN
SPDO
CDO
CTFN
CPDO
PHC
A/L
LSL
BVL
GAZETTE
PATLVE
MATLVE
BONUS
Click to expand...
Hi MeaclH,

Here's what I would do with that list:

Excel Formula: 
=AND(SUM(--MAP({"OFF","OFF-U","EDO","X0930","TRAINING","ADHOC","FRCE","SGS","MTP","AV","SDO","STFN","SPDO","CDO","CTFN","CPDO","PHC","A/L","LSL","BVL","GAZETTE","PATLVE","MATLVE","BONUS"},LAMBDA(unwanted,$C1=unwanted)))=0,COUNTIF($C:$C,$C1)>1)

Bests regards,

Vincent
 
Upvote 0
kevin9999 said:
One way is to have a list (somewhere) of all the exceptions to the CF rule, then reference it in your CF formula. Something like the following:

CF.xlsm
CDE
25OFFExceptions
260530NCCOFF
27OFF-U
280530NCCEDO
29ADHOCX0930
30TRAINING
312130PL/SADHOC
322130PL/SFRCE
33SGSSGS
341330NCCMTP
351330NCCAV
36SDOSDO
37OFFSTFN
38SDOSPDO
39CDOCDO
400530PBCTFN
41SDOCPDO
42CDOPHC
43OFFA/L
44SDOLSL
45BVL
462130PBGAZETTE
472130PBPATLVE
48AVMATLVE
49OFFBONUS
50
51AV
521330PB
53
54AV
55OFF
56
57
580530PL/S
590530PL/S
60
61OFF
622130PB
632130PB
64OFF
65
66
671330BC
68
69
702130NCC
71
720530PL/S
731330PL/S
74
75
76OFF
77
78
792130BC
80
81
820530BC
83ADHOC
84FRCE
85SGS
86MTP
87ADHOC
88FRCE
89SGS
90MTP
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C25:C92Expression=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF($C$25:$C$92,C25)>1)textYES
Click to expand...
Works a treat mate! Thank you for taking the time.
One thing. I will have 14 columns for a fortnight; how to have it apply column by column? For reference, actual project mini sheet attached.
DIGITAL ROSTER - VERSION 3.0.xlsm
GHIJKLMNOPQRST
350530PB1330BC1330PB1330CFD1330PL/SEDO-UOFF-UOFF-U1330BC1330PB1330CFDOFF1330NRN1330NCC
361745NCC DEC
37
38OFF-UOFF-U0530CFD0530PL/SOFF-UOFF-U0530PB0530NCCOFF-U0530PB0530PL/S0530CFD0530PL/SOFF
39DEC 0130 EXT
40FWADEC 1745 EXTFWA
41OFF-UOFF-U2130NCC2130BC2130PB2130PB2130NCC2130PL/S2130BC2130PBEDOOFF2130PL/SOFF
42
43
441330BCOFF1330CFD1330PL/S1330NRN1330BC1330PB1330NCCOFFOFFOFF1330NRN1330BC1330PB
45EXT 0145 PL/S OK
46
472130PL/S2130BC2130PL/SOFF-UOFF-U2130NCC2130PL/SOFF-U2130PL/S2130BC2130PB2130NCCEDO-UOFF-U
48EXT 1730NCC OK
49
500600RGT0530NRN0530NRNOFF-UOFF0530NRN0530BC0530PB0530CFD0530PL/S0530NRN0530PBOFFOFF
51
52AT METROL
531400RGT1330PL/S1330NRN1330BCEDO-UOFF-UOFF-UOFF-UOFF-U1330NRN1330BCBONUSPHCPHC
54
55AT METROL
562130PB2130NCCOFF-UOFF-U2130NCC2130PL/S2130BC2130PB2130NCCOFF-UOFF-U2130BC2130PBPHC
57
58
59OFF-UOFF-UX0930SDO0530PB0530CFD2130PBOFF-U0530NRN0530BC0530PBOFF-U0530CFD0530PL/S
60
61
621330PL/S1330NRN1330BCOFFOFF1330NRN1330BC1330PB1330CFD1330PL/S1330NRN1330PL/SOFFOFF
63EXT 0930PL/S OK
64
65STFNSTFNSTFNSTFNEDOOFFOFFOFFOFF2130PL/S2130BC2130PB2130NCC2130PL/S
66
67
68OFFOFFSDO0530PB0530CFD0530PL/S0530NCC0530PL/S0530BCOFFOFF0530BC0530PB0530NCC
69DEC 0130 EXT
70DEC 1745 EXT
710530BC0530PBOFFEDO0530NRN0530BCOFFOFFAV0530NRNOFF0530NRN0530BC0530PB
72EXT 1745NCC OKEXT 0130PB OK
73
74OFF1330PB2130BCEDO-U1330CFD1330PL/SOFFOFF1330PL/S1330BC1330PB1330CFD1330PL/S1330BC
75
76
771330PB1330CFDOFF-UOFF-U1330PB1330CFD1330PL/S1330BC1330PB1330CFD1330PL/SEDOOFFOFF
78
79
802130NCC2130PL/S2130PB2130NCC2130PL/S2130BCOFF2130NCCOFFOFF2130NCC2130PL/S2130BC2130PB
81EXT 1730PB OK
82
83OFF-UOFF1330PL/S1330NRN1330BC1330PB1330NCC1330PL/S1330NRNEDOOFF1330BC1330PBOFF
84
85
86A/L0530CFD0530PL/S0530NRN0530BC0530PBOFFOFFOFFOFF0530CFD0530PL/S0530NRN0530BC
87EXT 1745PB OK
88
892130BC2130PBOFF2130PL/S2130BCOFFOFF2130BC2130PB2130NCC2130PL/SEDOOFF2130BC
90
91
920530NCC0530BC0530PB0530CFD0530PL/SOFF-U0530PL/S0530BC0530PB0530CFD0530BCOFF-UOFF-UOFF-U
93DEC 0130 EXT
94
FN 26JAN-08FEB25
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=AND(COUNTIF($AU$18:$AU$41,G19)=0,COUNTIF($G$19:$G$110,G19)>1)textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*EDO*"), COUNTIF(G19,"*EDO-U*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*OFF*"), COUNTIF(G19,"*OFF-U*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*A/L*"), COUNTIF(G19,"*BONUS*"), COUNTIF(G19,"*GAZETTE*"), COUNTIF(G19,"*LSL*"), COUNTIF(G19,"*MATLVE*"), COUNTIF(G19,"*PATLVE*"), COUNTIF(G19,"*PHC*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))textYES
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valueending with "?"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "AV"textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=G$3=TODAY()textNO
G2:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COUNTIF(DATA!$AV$4:$AV$16,G$3)=1textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COLUMN()=CELL("COL")textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "OK"textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "DEC"textNO
Cells with Data Validation
CellAllowCriteria
G35:T35List=DATA!$AF$4:$AF$55
G44:T44List=DATA!$AF$4:$AF$55
G47:T47List=DATA!$AF$4:$AF$55
G50:T50List=DATA!$AF$4:$AF$55
G53:T53List=DATA!$AF$4:$AF$55
G56:T56List=DATA!$AF$4:$AF$55
G59:T59List=DATA!$AF$4:$AF$55
G62:T62List=DATA!$AF$4:$AF$55
G65:T65List=DATA!$AF$4:$AF$55
G68:T68List=DATA!$AF$4:$AF$55
G71:T71List=DATA!$AF$4:$AF$55
G74:T74List=DATA!$AF$4:$AF$55
G77:T77List=DATA!$AF$4:$AF$55
G80:T80List=DATA!$AF$4:$AF$55
G83:T83List=DATA!$AF$4:$AF$55
G86:T86List=DATA!$AF$4:$AF$55
G89:T89List=DATA!$AF$4:$AF$55
G92:T92List=DATA!$AF$4:$AF$55
G38:T38List=DATA!$AF$4:$AF$55
G41:T41List=DATA!$AF$4:$AF$55
 
Upvote 0
MeaclH said:
Works a treat mate! Thank you for taking the time.
One thing. I will have 14 columns for a fortnight; how to have it apply column by column? For reference, actual project mini sheet attached.
DIGITAL ROSTER - VERSION 3.0.xlsm
GHIJKLMNOPQRST
350530PB1330BC1330PB1330CFD1330PL/SEDO-UOFF-UOFF-U1330BC1330PB1330CFDOFF1330NRN1330NCC
361745NCC DEC
37
38OFF-UOFF-U0530CFD0530PL/SOFF-UOFF-U0530PB0530NCCOFF-U0530PB0530PL/S0530CFD0530PL/SOFF
39DEC 0130 EXT
40FWADEC 1745 EXTFWA
41OFF-UOFF-U2130NCC2130BC2130PB2130PB2130NCC2130PL/S2130BC2130PBEDOOFF2130PL/SOFF
42
43
441330BCOFF1330CFD1330PL/S1330NRN1330BC1330PB1330NCCOFFOFFOFF1330NRN1330BC1330PB
45EXT 0145 PL/S OK
46
472130PL/S2130BC2130PL/SOFF-UOFF-U2130NCC2130PL/SOFF-U2130PL/S2130BC2130PB2130NCCEDO-UOFF-U
48EXT 1730NCC OK
49
500600RGT0530NRN0530NRNOFF-UOFF0530NRN0530BC0530PB0530CFD0530PL/S0530NRN0530PBOFFOFF
51
52AT METROL
531400RGT1330PL/S1330NRN1330BCEDO-UOFF-UOFF-UOFF-UOFF-U1330NRN1330BCBONUSPHCPHC
54
55AT METROL
562130PB2130NCCOFF-UOFF-U2130NCC2130PL/S2130BC2130PB2130NCCOFF-UOFF-U2130BC2130PBPHC
57
58
59OFF-UOFF-UX0930SDO0530PB0530CFD2130PBOFF-U0530NRN0530BC0530PBOFF-U0530CFD0530PL/S
60
61
621330PL/S1330NRN1330BCOFFOFF1330NRN1330BC1330PB1330CFD1330PL/S1330NRN1330PL/SOFFOFF
63EXT 0930PL/S OK
64
65STFNSTFNSTFNSTFNEDOOFFOFFOFFOFF2130PL/S2130BC2130PB2130NCC2130PL/S
66
67
68OFFOFFSDO0530PB0530CFD0530PL/S0530NCC0530PL/S0530BCOFFOFF0530BC0530PB0530NCC
69DEC 0130 EXT
70DEC 1745 EXT
710530BC0530PBOFFEDO0530NRN0530BCOFFOFFAV0530NRNOFF0530NRN0530BC0530PB
72EXT 1745NCC OKEXT 0130PB OK
73
74OFF1330PB2130BCEDO-U1330CFD1330PL/SOFFOFF1330PL/S1330BC1330PB1330CFD1330PL/S1330BC
75
76
771330PB1330CFDOFF-UOFF-U1330PB1330CFD1330PL/S1330BC1330PB1330CFD1330PL/SEDOOFFOFF
78
79
802130NCC2130PL/S2130PB2130NCC2130PL/S2130BCOFF2130NCCOFFOFF2130NCC2130PL/S2130BC2130PB
81EXT 1730PB OK
82
83OFF-UOFF1330PL/S1330NRN1330BC1330PB1330NCC1330PL/S1330NRNEDOOFF1330BC1330PBOFF
84
85
86A/L0530CFD0530PL/S0530NRN0530BC0530PBOFFOFFOFFOFF0530CFD0530PL/S0530NRN0530BC
87EXT 1745PB OK
88
892130BC2130PBOFF2130PL/S2130BCOFFOFF2130BC2130PB2130NCC2130PL/SEDOOFF2130BC
90
91
920530NCC0530BC0530PB0530CFD0530PL/SOFF-U0530PL/S0530BC0530PB0530CFD0530BCOFF-UOFF-UOFF-U
93DEC 0130 EXT
94
FN 26JAN-08FEB25
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=AND(COUNTIF($AU$18:$AU$41,G19)=0,COUNTIF($G$19:$G$110,G19)>1)textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*EDO*"), COUNTIF(G19,"*EDO-U*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*OFF*"), COUNTIF(G19,"*OFF-U*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*A/L*"), COUNTIF(G19,"*BONUS*"), COUNTIF(G19,"*GAZETTE*"), COUNTIF(G19,"*LSL*"), COUNTIF(G19,"*MATLVE*"), COUNTIF(G19,"*PATLVE*"), COUNTIF(G19,"*PHC*"))textYES
G19:T19,G22:T22,G25:T25,G28:T28,G31:T31,G35:T35,G38:T38,G41:T41,G44:T44,G47:T47,G50:T50,G53:T53,G56:T56,G59:T59,G62:T62,G65:T65,G68:T68,G71:T71,G74:T74,G77:T77,G80:T80,G83:T83,G86:T86,G89:T89,G92:T92,G96:T96,G99:T99,G102:T102,G105:T105,G108:T108,G112:T112Expression=OR(COUNTIF(G19,"*BLV*"), COUNTIF(G19,"*CDO*"), COUNTIF(G19,"*SDO*"), COUNTIF(G19,"*PDO*"), COUNTIF(G19,"*TFN*"))textYES
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valueending with "?"textNO
G35:T94,G96:T110,G112:T126,G22:T33,G19:T19,G20,H20:T21Cell Valuecontains "AV"textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=G$3=TODAY()textNO
G2:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COUNTIF(DATA!$AV$4:$AV$16,G$3)=1textNO
G1:T3,G11:T14,G16:T17,G19:T33,G35:T94,G96:T110,G112:T126,G5:T9Expression=COLUMN()=CELL("COL")textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "OK"textNO
G23:T23,G26:T26,G29:T29,G32:T32,G36:T36,G39:T39,G42:T42,G45:T45,G48:T48,G51:T51,G54:T54,G57:T57,G60:T60,G63:T63,G66:T66,G69:T69,G72:T72,G75:T75,G78:T78,G81:T81,G84:T84,G87:T87,G90:T90,G93:T93,G97:T97,G100:T100,G103:T103,G106:T106,G109:T109,G113:T113Cell Valuecontains "DEC"textNO
Cells with Data Validation
CellAllowCriteria
G35:T35List=DATA!$AF$4:$AF$55
G44:T44List=DATA!$AF$4:$AF$55
G47:T47List=DATA!$AF$4:$AF$55
G50:T50List=DATA!$AF$4:$AF$55
G53:T53List=DATA!$AF$4:$AF$55
G56:T56List=DATA!$AF$4:$AF$55
G59:T59List=DATA!$AF$4:$AF$55
G62:T62List=DATA!$AF$4:$AF$55
G65:T65List=DATA!$AF$4:$AF$55
G68:T68List=DATA!$AF$4:$AF$55
G71:T71List=DATA!$AF$4:$AF$55
G74:T74List=DATA!$AF$4:$AF$55
G77:T77List=DATA!$AF$4:$AF$55
G80:T80List=DATA!$AF$4:$AF$55
G83:T83List=DATA!$AF$4:$AF$55
G86:T86List=DATA!$AF$4:$AF$55
G89:T89List=DATA!$AF$4:$AF$55
G92:T92List=DATA!$AF$4:$AF$55
G38:T38List=DATA!$AF$4:$AF$55
G41:T41List=DATA!$AF$4:$AF$55
Click to expand...
Maybe it's an issue with the cells I have applied it to and the absolute references. I only want it to apply to cells in row 19,22,25,28..etc in each column.
 
Upvote 0
coulombevin said:
Hi MeaclH,

Here's what I would do with that list:

Excel Formula: 
=AND(SUM(--MAP({"OFF","OFF-U","EDO","X0930","TRAINING","ADHOC","FRCE","SGS","MTP","AV","SDO","STFN","SPDO","CDO","CTFN","CPDO","PHC","A/L","LSL","BVL","GAZETTE","PATLVE","MATLVE","BONUS"},LAMBDA(unwanted,$C1=unwanted)))=0,COUNTIF($C:$C,$C1)>1)

Bests regards,

Vincent
Click to expand...
Hi Vincent,

Thanks for your contributions! I'm a bit lost, is this a CF formula? I am getting a response "you may not use LAMBA functions for CF"

Cheers,
Hayden
 
Upvote 0
To apply it easily to multiple columns, leave the absolute reference in the first part of the formula alone, but take the absolute references off the second part, like this:

Rich (BB code): 
=AND(COUNTIF($E$26:$E$49,C25)=0,COUNTIF(C25:C92,C25)>1)

You can change the addresses to your actual locations first of course. Then highlight the first column of interest & copy the formats across to your entire range.
 
Upvote 0
MeaclH said:
Hi Vincent,

Thanks for your contributions! I'm a bit lost, is this a CF formula? I am getting a response "you may not use LAMBA functions for CF"

Cheers,
Hayden
Click to expand...
Hi Hayden,

You're right by adding a lambda you end up with an error.

cheers,

Vincent
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Copy/Paste special an assortment of rows
Replies
6
Views
1K
anastabation
A

Forum statistics

Threads
1,226,063
Messages
6,188,653
Members
453,489
Latest member
jessrw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top