Please Help me. Searching for a certain text in a column, and entering yes/no in a certain cell

djbadabing1

New Member
Joined
Jun 24, 2014
Messages
6
Im am looking to create a schedule template where:
Row #1 has Monday - Sunday
Column A has a list of employee names

The employees work at certain stations 1-15 that will be placed under certain days of the week in their row.

I want a reference column to the right of this table that lists all stations in numerical order 1-15, and another column to the right of that column that has a header of "check". When station number 4 is entered in a column for monday, I want YES to appear in the check column next to station 4. And the same for all other stations. It will be a way for me to easily verify that all stations are scheduled for that day. I need to have a verification section for each day of the week. Im not great with formulas, I've tried IF, VLOOKUP, SEARCH, and I'm having no luck with my basic knowledge of excel. That is why I came here. I would appreciate any help with this formula, as I have too many hours wasted with no end result. Thank you for your time.
 
Okay, I see (the posted layout was not all that clear to me). See if this formula in K2 copied across and down does what you want...

=IF(COUNTIF(B:B,$J2&"*"),"YES","")

It works, but YES is entered for multiple stations. When a "1" duplicated in the field. Example: If I enter #12 on wednesday for Brad, #1 & #12 will have a YES input.
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
It works, but YES is entered for multiple stations. When a "1" duplicated in the field. Example: If I enter #12 on wednesday for Brad, #1 & #12 will have a YES input.
Sorry, I forgot the slash...

=IF(COUNTIF(B:B,$J2&"/*"),"YES","")
 
Upvote 0
You could have used as well =IF(ISNUMBER(MATCH($J2,B$2:B$6,0)),"YES","")
 
Upvote 0

Forum statistics

Threads
1,226,739
Messages
6,192,739
Members
453,755
Latest member
IQBS

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