Help with conditional formatting

Pharmacisticus

New Member
Joined
Sep 12, 2018
Messages
13
Hi,

I need help with conditional formatting...
I have attached a pic of my excel document that I will use to describe my issue.
I want to know if it is possible to do the following...
In the pic, I have a cell AA3 with the column heading LEAVE.
This cell contains the following text "Manoj LSL Leo A/L Michelle LSL"

I want to know is it possible to set up conditional formatting (or similar) so that if any of those names "Manoj, Leo, or Michelle" appear in that row the cell that those names are in would be highlighted?
i.e. so I don't roster people off when they have leave!

I would also want this formatting/formula etc to be able to run in each of the following cells in column AA so that the changes in leave can be accounted for.

I'll leave this in your awesome hands,

Many thanks,

Pharmacisticus
 

Attachments

  • Roster from hell.JPG
    Roster from hell.JPG
    59 KB · Views: 10

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
This works for me

Select columns M:Z (whole columns)
Click on Conditional Formatting \ New Rule \ Use formula ...

=COUNTIF($AA1,"*"&LEFT(M1,FIND(CHAR(10),M1)-1)&"*")>0

Select preferred formatting and confirm

Rota CF.jpg



WARNING
Expect some false positives if both names Peter and Peter X are valid and Peter is in the rota with Peter X on LEAVE (but not the other way round)
- Excel is looking to Match *Peter* and Peter X matches
- Excel is looking to Match *Peter X* and Peter is NOT a match

You will have to live with that - unless some else comes up with something


COMMENT
Using the whole column in CF is not necessarily good practice but allows you to test the method
If your worksheet never contains more than 1000 rows , then select M1 to Z1000 rather than whole columns etc
 
Upvote 0
Hi Yongle,

Thank you for your help with this question!
I have tried your solution but with limited success, perhaps due to the poor pic in my first post.
I have followed the instructions in your post regarding pasting worksheets using BB code below.
Would you mind terribly having another look at this for me?

I apologise for the layout of this document, it's something I have inherited and am trying to fix it as best I can.

Kind Regards,
Josh

Roster Help.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1WODONGA ROSTERALBURY ROSTER
2DateDayPBSRehab and GEM Acute and COUEmergencyDisp PBSImprestAsepticOrdersExtra PBSMed 1Med 2Surg 2 & PaedSurg 1ICUMental HealthEmergencyAMSMed SafetyDisp PBSImprest 1 In PatientImprest 2 In Patient OrdersExtraLEAVEDateDay
3####MonSheila 08:30-17:00Jillian 08:30-17:00Justin 08:30 -17:00Sam 08:30 - 17:00Dean 08:30-17:00Karen 09:00-17:00TBC Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 09:00 - 17:30ChenStewart 08:15 -16:45Christy 09:00-16:45Jillian 08:15-16:45GlenJustin 08:15-16:45Erin 08:15-16:45Justin 08:15-16:45Angie 08:15 - 16:45Sarah 08:15 - 16:45Francine 08:15-16:45Catherine 08:30-17:00Chen LSL Leonard A/L Michelle LSL####Mon
4####TueSheila 08:30-17:00Jillian 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Sam 08:30 - 17:00Dean 09:00-17:00 Sharylanda 08:30 - 17:00Manuel 08:15 - 16:45LeonardJustin 09:00-17:30Christy 09:00-16:45Glen 09:00-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45Justin 08:15-16:45Justin 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15 Sarah 08:15 - 16:45Francine 08:15-16:45Chen LSL Leonard A/L####Tue
51-AprWedBen 08:30-17:00Jillian 08:30-17:00Dennis 08:30-15:00Ben 08:30 - 17:00Catherine 08:30-17:00Dean 09:00-17:00Karen 09:00-17:00TBC Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45Justin 08:15-16:45Glen 09:00-17:30GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Angie 08:15 - 16:45Donne 08:15-16:15Sarah 08:15 - 16:45Francine 08:15-16:45Chen LSL Leonard A/L1-AprWed
62-AprThuBen 08:30-17:00Jillian 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Catherine 08:30-17:00Dean 09:00-17:00Karen 09:00-17:00David 08:30-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45Glen 08:15-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCAngie 08:15 - 16:45Sarah 08:15 - 16:45Renee 08:15 - 16:45Chen LSL Leonard A/L Michelle LSL2-AprThu
73-AprFriBen 08:30-17:00Jillian 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Catherine 08:30-17:00Dean 09:00-17:00Karen 09:00-17:00Donne 09:00-17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45Glen 09:00-17:15 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Angie 08:15 - 16:45Sarah 08:15 - 16:45Renee 08:15 - 16:45Chen LSL Leonard A/L Michelle LSL3-AprFri
84-AprSat4-AprSat
95-AprSun5-AprSun
106-AprMonSheila 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Karen 09:00-17:00Sam 08:30 - 17:00 Sharylanda 08:30 - 17:00Dean 08:30-17:00Manuel 08:15-16:45ChenStewart 08:15 -16:45Christy 09:00-16:45Glen 09:00-15:00 Kate 08:15 - 16:45GlenAnita 08:15 - 16:45Justin 08:15-16:45Erin 08:15-16:45Justin 08:15-16:45Francine 08:15-16:45Angie 08:15 - 16:45Sarah 08:15 - 16:45Catherine 08:30-17:00Chen LSL Leonard A/L Dennis ADO Michelle A/L6-AprMon
117-AprTueSheila 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00 Sharylanda 08:30 - 17:00Sam 08:30 - 17:00Dean 09:00-17:00Manuel 08:15 - 16:45LeonardJustin 09:00-17:30Christy 09:00-16:45Glen 09:00-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45Justin 08:15-16:45Justin 08:15-16:45Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15 Sarah 08:15 - 16:45Chen LSL Leonard A/L7-AprTue
128-AprWedBen 08:30-17:00JocDennis 08:30-15:00Ben 08:30 - 17:00Karen 09:00-17:00Catherine 08:30-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45Justin 08:15-16:45Glen 09:00-17:30 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15Sarah 08:15 - 16:45Chen LSL Leonard A/L Dennis Study > 3pm8-AprWed
139-AprThuBen 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Karen 09:00-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45Glen 08:15-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCRenee 08:15 - 16:45CatherineDonne 08:15-16:15Sarah 08:15 - 16:45Chen LSL Leonard A/L Michelle A/L Angie ADO Catherine A/L9-AprThu
1410-AprFriPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPH10-AprFri
1511-AprSatPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPH11-AprSat
1612-AprSun12-AprSun
1713-AprMonPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPHPH13-AprMon
1814-AprTueSheila 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00 Sharylanda 08:30 - 17:00Sam 08:30 - 17:00Dean 09:00-17:00Manuel 08:15 - 16:45Justin 09:00-17:30Christy 09:00-16:45Glen 09:00-15:00 GlenAnita 08:15 - 16:45Justin 08:15-16:45Justin 08:15-16:45Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15 Catherine 08:30-17:00Chen LSL Leonard A/L Jillian A/L Sarah ADO14-AprTue
1915-AprWedBen 08:30-17:00Kate 08:30-17:00Dennis 08:30-15:00Ben 08:30 - 17:00Karen 09:00-17:00Catherine 08:30-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45Justin 08:15-16:45Glen 09:00-17:30GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15Sarah 08:15 - 16:45Chen LSL Leonard A/L Jillian AL Dennis Study > 3pm15-AprWed
2016-AprThuBen 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Karen 09:00-17:00Catherine 08:30-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 - 15:00 Karen 08:15-16:45Glen 08:15-15:00 GlenAnita 08:15 - 16:45TBCRenee 08:15 - 16:45Angie 08:15 - 16:45Sarah 08:15 - 16:45Chen LSL Leonard A/L Jillian A/L Michelle A/L Justin Cert 416-AprThu
2117-AprFriBen 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Catherine 08:30-17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45Glen 09:00-17:15 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Renee 08:15 - 16:45Angie 08:15 - 16:45Michelle 08:30 - 17:00Sarah 08:15 - 16:45Chen LSL Leonard A/L Karen J A/L Donne A/L17-AprFri
2218-AprSat18-AprSat
2319-AprSun19-AprSun
24####MonSheila 08:30-17:00Kate 08:30-17:00Justin 08:30 -17:00Ben 08:30 - 15:00Karen 09:00-17:00Sam 08:30 - 17:00 Sharylanda 08:30 - 17:00Dean 08:30-17:00Manuel 09:00 - 17:30Stewart 08:15 -16:45Christy 09:00-16:45Glen 09:00-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45Justin 08:15-16:45Erin 08:15-16:45Justin 08:15-16:45Francine 08:15-16:45Angie 08:15 - 16:45Michelle 08:30 - 17:00Sarah 08:15 - 16:45Catherine 08:30-17:00Chen LSL Donne A/L Erin A/L Manuel ADO####Mon
2521-AprTueSheila 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00 Sharylanda 08:30 - 17:00Sam 08:30 - 17:00Dean 09:00-17:00Manuel 08:15 - 16:45Justin 09:00-17:30Christy 09:00-16:45Glen 09:00-17:30GlenAnita 08:15 - 16:45Justin 08:15-16:45Justin 08:15-16:45Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15 Sarah 08:15 - 16:45Chen LSL Donne A/L Ben A/L21-AprTue
26####WedBen 08:30-17:00Kate 08:30-17:00Dennis 08:30-15:00Ben 08:30 - 17:00Karen 09:00-17:00Catherine 08:30-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45Justin 08:15-16:45Glen 08:15-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15Sarah 08:15 - 16:45Chen LSL Donne A/L Ben A/L Kate G ADO Dennis Study > 3pm####Wed
27####ThuBen 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Karen 09:00-17:00Catherine 08:30-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45Glen 09:00-17:15 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCRenee 08:15 - 16:45Angie 08:15 - 16:45Michelle 08:30 - 17:00Sarah 08:15 - 16:45Chen LSL Ben A/L####Thu
28####FriBen 08:30-17:00Kate 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Donne 08:15-16:15Catherine 08:30-17:00Karen 09:00-17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45 Karen 08:15-16:45GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Renee 08:15 - 16:45Angie 08:15 - 16:45Michelle 08:30 - 17:00Sarah 08:15 - 16:45Chen LSL Ben A/L####Fri
29####Sat####Sat
30####Sun####Sun
31####MonSheila 08:30-17:00Jillian 08:30-17:00Justin 08:30 -17:00Karen 09:00-17:00Sam 08:30 - 17:00 Sharylanda 08:30 - 17:00Dean 08:30-17:00Manuel 09:00 - 17:30Stewart 08:15 -16:45Christy 09:00-16:45Jillian 08:15-16:45GlenJustin 08:15-16:45Erin 08:15-16:45Justin 08:15-16:45Francine 08:15-16:45Angie 08:15 - 16:45Michelle 08:30 - 17:00Sarah 08:15 - 16:45Catherine 08:30-17:00Chen A/L####Mon
32####TueSheila 08:30-17:00Jillian 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00 Sharylanda 08:30 - 17:00Sam 08:30 - 17:00Dean 09:00-17:00Manuel 08:15 - 16:45Justin 09:00-17:30Christy 09:00-16:45Glen 09:00-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45Justin 08:15-16:45Justin 08:15-16:45Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15 Sarah 08:15 - 16:45Chen A/L####Tue
33####WedBen 08:30-17:00Jillian 08:30-17:00Dennis 08:30-15:00Ben 08:30 - 17:00Karen 09:00-17:00Catherine 08:30-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45Justin 08:15-16:45Glen 09:00-17:30GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Francine 08:15-16:45Angie 08:15 - 16:45Donne 08:15-16:15Sarah 08:15 - 16:45Chen A/L Jillian ADO Dennis Study >3pm####Wed
34####ThuBen 08:30-17:00Jillian 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Karen 09:00-17:00Catherine 08:30-17:00 Sharylanda 08:30 - 17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45Glen 08:15-15:00 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCRenee 08:15 - 16:45Angie 08:15 - 16:45Michelle 08:30 - 17:00Sarah 08:15 - 16:45Chen A/L####Thu
351-MayFriBen 08:30-17:00Jillian 08:30-17:00Dennis 08:30-17:00Ben 08:30 - 17:00Donne 08:15-16:15Catherine 08:30-17:00Karen 09:00-17:00Dean 09:00-17:00 Sharylanda (iPharm Upgrade) 08:30 - 17:00Manuel 08:15 - 16:45Stewart 08:15 -16:45 Karen 08:15-16:45Glen 09:00-17:15 Jillian 08:15-16:45GlenAnita 08:15 - 16:45TBCChristy 08:15-17:30Renee 08:15 - 16:45Angie 08:15 - 16:45Michelle 08:30 - 17:00Sarah 08:15 - 16:45Chen A/L Glen Study 1-MayFri
Apr 2020 - Draft (2)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C1:Z35Expression=COUNTIF($AA1,"*"&LEFT(M1,FIND(CHAR(10),M1)-1)&"*")>0textNO
 
Upvote 0
1. Let me explain how the formula I gave you works
It looks for the first line break introduced by {ALT}{ENTER} which is CHAR(10) and then searches for the text to the left of that in column AA

2. There are some problems with your data which you need to fix to make this work
- some of your cells contain CHAR(10) as the FIRST character eg Sharylanda (repeatedly)
- unfortunately that results in Excel trying to find "**" which means EVERYTHING is found - all are false positives!

3. More than one name can appear in the cell
(I already knew that this would be an issue but must deal with that separately)
- to find the second name will require an additional CF rule which is more complicated
- the formula needs to find the 2nd and 3rd incidence of CHAR(10) and look for the text between
- something like =MID(C1,=FIND("~",SUBSTITUTE(C1,(10),"~",2)) ,FIND("~",SUBSTITUTE(C1,(10),"~",3)) -FIND("~",SUBSTITUTE(C1,(10),"~",2)) )
- I am busy for a few days but will try to find a simpler method

In the meantime
95% of your problem is solved if the first name is found

Select columns C:Z and use this formula
- the original solution applied to all columns)
=COUNTIF($AA1,"*"&LEFT(C1,FIND(CHAR(10),C1)-1)&"*")>0

Clean up your data by dealing with what I mentioned in 2 above
If you put formula below in another cell row 3 (to the right of your data) ...
... and copy it across (24 columns to Match C:Z) ) ...
... and down in all used rows ...
...it will show the the reference of every cell with that problem
=IFERROR(IF(CODE(C3)=10,ADDRESS(ROW(C3),COLUMN(C3),4),""),"")

If you ensure that the input in cells is consistently NAME followed by {ALT}{ENTER} then the FIRST name is always found

I will be in touch again next week
Please confirm that you are happy with this approach
 
Last edited:
Upvote 0
After you have dealt with what is in previous post (and not before !!)

To catch the 2nd name
Add a 2nd rule and format it differently so that you can
- identify any issues during testing
- and know (when live) that it is the 2nd name that is being rostered while on leave

The formula is
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2))-1)&"*")>0

------------------------------------------------------------------------------------------------------
A couple of foreseeable issues:
------------------------------------------------------------------------------------------------------

Now ...
For the second name to be matched the value in the cell must be entered like this
Name1
CHAR((10)
Time
CHAR((10)
Name2
CHAR(10)

So ...
The problem that gives is that a cell that looks like this will not work
The first name is picked up by the original rule, but the 2nd name is in the wrong place

Sharylanda
(iPharm Upgrade)
08:30 - 12:00
Karen
12:00-17:00

To deal with that eventuality (there are no examples in your data) but presumably there is no logical reason why it could never happen
The formula becomes
=OR(COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3))-1)&"*")>0,COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2))-1)&"*")>0)

Simplified all the formula does is this
=OR( does Line 4 match, does line 3 match)

This is what I would do: ...
Another way to deal with it would be to leave the 2nd rule as
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",2))-1)&"*")>0
and add the other rule separately but use the same format as 2nd rule
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",3))-1)&"*")>0

How many names could there be?
I do not know if you sometimes have 3 or even 4 names in the same cell, but if that is the case you could (in theory) keep adding extra rules to make sure each one is matched
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",5)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",4))-1)&"*")>0
=COUNTIF($AA1,"*"&MID(C1,1+FIND("~",SUBSTITUTE(C1,CHAR(10),"~",5)),FIND("~",SUBSTITUTE(C1,CHAR(10),"~",6)) -FIND("~",SUBSTITUTE(C1,CHAR(10),"~",5))-1)&"*")>0
etc
If you think that would be valuable, then DO NOT CONDITIONALLY FORMAT WHOLE COLUMNS but select a "large enough" range of cells - otherwise you will end up with a VERY slow performing workbook

The quality of input data
Everything I have provided only works if there is good control exercised over the input of data
- sloppy input will result in names not matching

Spaces
I would like to amend all the formulas I have provided to deal with someone entering a space AFTER a name and before CHAR(10)
It occurs to me that
- it will not result in a match as things stand
- it will not be obvious because you cannot see the space when looking in the cell

Q
What happens when there are 2 people with the same name ?
Q Do you use Sam1, Sam2 ?
Q Are there ever spaces in any names for any reason ?
Unless you standardise on names without a space I cannot deal with that potential typo
- I will need your thoughts on this in due course
 
Upvote 0
This could also be handled very easily with VBA as illustrated below (- and even if you do not want to use VBA -) test it so that you can see it working
In the live environment you would want it working differently, but this shows you that the matching works

The code
- is triggered when user selects a cell
- loops through all names in "Personnel" and looks for string matching the name in the active cell
- if match is found the same test is carried out in cell in column AA
- if rostered is on leave then message box tells you that (with a separate message box for each name)

To test
1. Create a new workboook containing a single roster sheet as per post#3

2. Right click on sheet tab \ View Code \ paste code into that window \click in top right corner to close VBA window
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > 26 Then Exit Sub
    Set Target = Target.Cells(1, 1)
    Dim person As Range
    For Each person In Sheets("Personnel").Range("A2", Sheets("Personnel").Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants)
        If InStr(Target, person) Then
            If InStr(Cells(Target.Row, "AA"), person) Then MsgBox Target.Address(0, 0) & vbTab & person
        End If
    Next person
End Sub
It will look similar to this (I amended the code after creating picture)
SheetModule.jpg


3. Add a sheet and rename it "Personnel"

4. Copy list below into column A in that sheet
Book1
A
1Personnel
2Ben
3Kate
4Dennis
5Karen
6Catherine
7Sharylanda
8Dean
9Manuel
10Stewart
11Justin
12Glen
13Anita
14Christy
15Francine
16Angie
17Donne
18Sarah
19Chen
20Jillian
21Sheila
22Leonard
Personnel


5. Select a cell in the roster
- message box appearing tells you what you need to know

The code is very simple for illustration only.
If you edit a cell and roster someone who is on leave ... select a different cell and then select the original cell again to trigger the code to test the original cell

CAVEAT
The list of names is not complete and may differ from the names on your sheet
- make sure that the names that you are testing appear in sheet "Personnel"
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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