Question description:
This user has given permission to use the problem statement for this
blog.
I need help with conditional formatting to highlight a cell if any of the names appear in a column list of names
Solved by O. J. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
27/03/2018 - 01:45
hi there!
User
27/03/2018 - 01:45
HI
Excelchat Expert
27/03/2018 - 01:45
Are you able to load the file into the Google Sheet window? I am sure I can help you with your conditional formatting question...
Excelchat Expert
27/03/2018 - 01:45
or you could attach the file as well - whatever is easier.
User
27/03/2018 - 01:46
I can quickly write up an example sheet in excel
Excelchat Expert
27/03/2018 - 01:46
beautiful
Excelchat Expert
27/03/2018 - 01:47
are you using Excel or Google Sheets? (the conditional formatting procedures are a touch different between the 2)
User
27/03/2018 - 01:49
so I want conditional formatting for cell A1. I am using excel. I want cell A1 to change color if any of the items are in the list. So if I type "a" in cell A1, I want conditional formatting to apply
User
27/03/2018 - 01:49
same goes for b, c, and d
Excelchat Expert
27/03/2018 - 01:49
gotcha
Excelchat Expert
27/03/2018 - 01:50
let me reproduce this little example in Excel so I can figure it out - shouldn't be long!
Excelchat Expert
27/03/2018 - 01:53
how long could the list of names be?
User
27/03/2018 - 01:53
25
User
27/03/2018 - 01:53
on average
Excelchat Expert
27/03/2018 - 01:55
tricky!
Excelchat Expert
27/03/2018 - 01:55
ok I have it :)
Excelchat Expert
27/03/2018 - 01:55
i had to use a secondary formula to base the conditional formatting on
Excelchat Expert
27/03/2018 - 01:55
one sec ill attach a file and walk you through it
Excelchat Expert
27/03/2018 - 01:55
you can apply the solution to your file
User
27/03/2018 - 01:55
ok great
Excelchat Expert
27/03/2018 - 01:57
actually never mind - i added the formula directly to the conditional formatting (even better)
User
27/03/2018 - 01:57
lets see it!
Excelchat Expert
27/03/2018 - 01:58
[Uploaded an Excel file]
Excelchat Expert
27/03/2018 - 01:58
ok check that out
Excelchat Expert
27/03/2018 - 01:58
you will have to tweak the conditional formatting formula to match the cell that you're entering the name as well as match the list of names
Excelchat Expert
27/03/2018 - 01:59
=IF(ISNUMBER(MATCH(A1,E2:E100,0)),1,0) So the A1 needs to be whatever cell you are entering the name into. And the E2:E100 is the range of cells with the names.
User
27/03/2018 - 02:00
That is the equation needed for the "new rule" for conditional formatting?
Excelchat Expert
27/03/2018 - 02:00
yep you'd go to new rule, and then select "use a formula to determine which cells.... etc."
User
27/03/2018 - 02:01
Great! seems like it should work for my purposes. I'll give it a shot!
Excelchat Expert
27/03/2018 - 02:01
do you want to test now while I'm here?
Excelchat Expert
27/03/2018 - 02:01
you can always ping back of course as well - whatever is easier for you
User
27/03/2018 - 02:02
ok just a sec
Excelchat Expert
27/03/2018 - 02:02
sure thing
Excelchat Expert
27/03/2018 - 02:04
the timer will expire though unless you press the extend 20 minutes
Excelchat Expert
27/03/2018 - 02:04
best of luck if I loose you!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
