I need to create a formula that generate a list of names based on specific criteria. I will do my best to describe what I have already done, and what is going wrong:
1. I have created a "data sheet" that lists (among other things) student's names and which classroom teacher they go to:
2. I have created another sheet to list all the students that are associated with Teacher 1, using an IF function to automatically place the students' names on the list:
3. This works fine, until a student moves from one classroom to another. In the first screenshot above, if I change Student 8 (row 9) to work with Teacher 2, their name appears in row 9 on the second sheet:
How can I make changes on the data sheet, have the Teacher sheet automatically update, yet keep the names from populating into the same row they originated from? I have tried using absolute values in the function, but that does not seem to make a difference. Is an IF function appropriate in this case? Is there something better/more useful that I should be using?
You can use one of the Lookup/Match functions to grab student names and consolidate names for each teacher in separate tables. It will by dynamic.
First, I have to comment on your screen shots (good ones). It appears that you haven't yet discovered Header Rows. Column Titles and things like Teacher Name for the breakout table should be in one or more Header Rows. It makes things easier in the programming department.
Next you will be adding a column to your Data Sheet table so that you can index the students by their teacher and their order in the list. This combination of factors makes it possible to do the breakouts.
Here's an approach using the Match function to find what row the student's name is in and using the Index function to go and get that name:
The formula in the index column is:
=B&" - "&COUNTIF(OFFSET($B$1, 0,0, ROW()), B)
And, the formula in the list of students under the teacher's name is:
=INDEX(DataTbl, MATCH($A$1&" - "&ROW()-1, DataTbl :: C, 0), 1)
The red triangles indicate that there are more rows than students. A small addition to the expression for finding the student name can fix this:
=IF(COUNTIF(DataTbl :: B,$A$1)>ROW()-2,INDEX(DataTbl, MATCH($A$1&" - "&ROW()-1, DataTbl :: C, 0), 1), ".")
I didn't give you this at first because the expression is already tough enough to read.
Now you will have: