Apple Event: May 7th at 7 am PT

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Level 1

24 points

Generating a list of names

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?

Numbers-OTHER, OS X Mountain Lion (10.8.3)

Posted on May 13, 2013 6:00 AM

Best reply

Jerrold Green1

Level 7

30,211 points

Posted on May 13, 2013 7:22 AM

Jo-Alex,

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:

Jerry

View in context

16 replies

Oct 3, 2013 4:26 PM in response to keyboardman3244

My guess would be that you have not provided the functions with the proper table names, but it's difficult to tell from here.

I suggest that you begin a new thread (Question) and begin with a clear and concise problem statement, including the names of your tables and pasted expressions that aren't working for you. As I stated previously in this thread, close doesn't count, so don't retype your expressions, Copy and Paste them.

Jerry

Generating a list of names