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

[djbadabing1]

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.

 

[cyrilbrd]

Similar to this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Mon	Tue	Wed	Thur	Fri	Sat	Sun		station	Mon	Tue	Wed	Thur	Fri	Sat	Sun
2	john	1		3		6				1	YES
3	mark		2							2		YES
4	phil	8			4					3			YES
5	david	5								4				YES
6	matt			9				7		5	YES
7										6					YES
8										7							YES
9										8	YES
10										9			YES
djbadabing1

 

[djbadabing1]

That is exactly what I am looking for. You brought a tear to my eyes! lol

 

[Rick Rothstein]

That is exactly what I am looking for. You brought a tear to my eyes! lol

Put this formula in cell K2...

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

Then copy it across to cell Q2, then copy the range K2:Q2 down to the bottom of the list of stations you have in Column J.

 

[djbadabing1]

Put this formula in cell K2...

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

Then copy it across to cell Q2, then copy the range K2:Q2 down to the bottom of the list of stations you have in Column J.

That worked perfectly! The only issue that I'm having, is that I put a time in the same box as their time to come in example: #12/9:00pm If i use #12 by itself, the formula works fine. but if I add anything else to that box, it rejects the formula. I will be using the number sign in front of the station if i can get it to just search for #XX? Let me know what you think. Thanks again for helping out. You guys are crazy good at this stuff.

 

[cyrilbrd]

please provide small sample with expected result.
you can copy paste small sample directly into your reply.

 

[djbadabing1]

please provide small sample with expected result.
you can copy paste small sample directly into your reply.

[TABLE="width: 712"]
<colgroup><col><col span="4"><col><col span="2"><col><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]MON[/TD]
[TD]TUES[/TD]
[TD]WED[/TD]
[TD]THURS[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[TD]SUN[/TD]
[TD][/TD]
[TD]Station[/TD]
[TD]Mon[/TD]
[TD]Tues[/TD]
[TD]Wed[/TD]
[TD]Thurs[/TD]
[TD]Fri[/TD]
[TD]Sat[/TD]
[TD]Sun[/TD]
[/TR]
[TR]
[TD]ALEXANDRIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#3B/8:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ALISHA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#BVIP/9:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#2[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ANGELINA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#MSTREET/6:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BRAD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#9/9:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#3B[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BRANDON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#11/9:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CANDACE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#BOCA/10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#5[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CHRISTOPHER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#RUN1/6:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#7[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]CHRIS W.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DR/8:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#8[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]ERIK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#2/8:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]GENAE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#12/9:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#10[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]HEATHER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#15/9:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#11[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JENNI[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#1/8:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JERRICA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DOOR/10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]JOSH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#7/7:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KAREN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#8/7:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#MALF[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KAYLA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#DJ/10:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#MALB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#MALB/8:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]#TIKI[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

And what would be in the cells of Columns B thru H... the number (or text) by itself or the number (or text) with the # sign in front of it?

 

[djbadabing1]

And what would be in the cells of Columns B thru H... the number (or text) by itself or the number (or text) with the # sign in front of it?

The example that I posted, is the text that would be in row "F". I gave an example for "friday" only, the other rows would look similar to row "F" (friday).

 

[Rick Rothstein]

The example that I posted, is the text that would be in row "F". I gave an example for "friday" only, the other rows would look similar to row "F" (friday).

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","")