ELL Classes

knaimi

New Member
Joined
Jun 23, 2013
Messages
47
I have a spreadsheet in excel 2016 that contains the names of all the students in my school. The sheet has the students’ names, student id (which is unique for each student). One of the columns in the spreadsheet is ELL (Column I) which refers to English Language Learner. If there is a “Yes” in that column, it means that the student is actually eligible to receive ESL services. The majority of the ELL students are scheduled to take one of the following classes: ELL 1A, ELL 1B, ELL 2A, ELL 2B, … , ELL 4A, or ELL 4B. Some of the ELL with “Yes” may not take any of these classes if their English language level is high.

I need to add a formula in Column H (Tab 1) where if there is a “Yes” in any of the cells in column I (ELL), then it should check the student ID (Column D) and go to another tab called “Schedule” and find the same student ID (Column C) to see if the students is taking any of the classes listed above and place that class in column G, if the student doesn’t have any of those classes, the formula should place “Yes” in column H. Notice that the student schedule has 6 lines, each line represent the periods the student is scheduled in

Please see Sample below of both tabs:

Tab 1
Column AColumn BColumn CColumn DColumn EColumn FColumn GColumn HColumn I
1State IDCounselorGradeI.D.LastFirstGenderELL2ELL
284029835371020085309MaleYesYes
303367931341120103578FemaleELL2Yes
456263014051020103579FemaleELL2Yes
562410971971020080804MaleYesYes
696247636661120079035FemaleELL3Yes
769799952751220056273Female
881768858581220061292Female
984651357001220054334MaleYesYes

<tbody>
</tbody>

Tab 2
Column AColumn BColumn CColumn DColumn EColumn FColumn G
1CounselorGradeStudent IDStudent NamePeriodCourse-SectionCourse Title
21020085309Student 11613130-11Physics S1
31020085309Student 12612070-4US Economics
41020085309Student 13618640T-1Auto Brake/Steering 5
51020085309Student 14618640T-1Auto Brake/Steering 5
61020085309Student 15611050-2Language Arts 5
71020085309Student 16614029-7Algebra 2 B S1
81120103578Student 21614820-31Algebra 1 S1
91120103578Student 22611836-224ELL 2B
101120103578Student 23611804-23Reading Improvement
111120103578Student 24611836-224ELL 2B
121120103578Student 25612833-55World History S1
131120103578Student 26613800-76Biology S1

<tbody>
</tbody>
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Forum statistics

Threads
1,213,543
Messages
6,114,236
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