I am working with excel 2000 and windows xp home. I have a preschool that I am building a roster for. I

want to enter all the students information on one

worksheet, and then based upon what class they are

enrolled in send some of their information (right now

just their first and last name) to a separate worksheet

that I will use for attendance in that class.I was thinking of using column A for their last name,

column B for their first name and column H for the

class they are in.So based on what is in column H the information in

column A and B of that same row would be sent to a

separate worksheet specific to the class listed in

column H.If someone could point me in the right direction to

learn how to do this it would be greatly appreciated.Thank You,

Neil

This can be done fairly easily with a macro and if you are willing to try it, I can suggest some code. If you want to do it strictly with formulae, it can be done, but there are a few extra steps.

Excel provides a number of

lookupfunctions, but unfortunately, they all find the first occurrence of the value you are looking up within the given range. Assuming your last names are sorted in alphabetical order, you are going to have class designations spread out throughout the list.Using a macro to loop through the list and match student names to a specific class designation is easy. However, to do it with a formula means that you have to create unique class designations for the lookup functions to find.

Here's one way to do it:

Let's say you have a list of last names in Column A and class designations in Column H. I'll use letters:

A H 1 Last 1 A 2 Last 2 B 3 Last 3 C 4 Last 4 A 5 Last 5 C 6 Last 6 B 7 Last 7 C 8 Last 8 B 9 Last 9 AHere's what you need to do:

Insert a column to the left of A, which will move your last names into B and class designations to I. (You can hide this new column A later if you want.)

Put this formula in A1 and drag it down:

=I1 & COUNTIF($I$1:I1,I1)

Note the use of the Absolute $I$1 and Relative I1 in the COUNTIF range. This is crucial.

What this will do is create class designations with a number representing each occurrence of the class, so your sheet now looks like this:

A B I 1 A1 Last 1 A 2 B1 Last 2 B 3 C1 Last 3 C 4 A2 Last 4 A 5 C2 Last 5 C 6 B2 Last 6 B 7 C3 Last 7 C 8 B3 Last 8 B 9 A3 Last 9 ANow you have a list of unique names for the lookup function to find, but the students are still associated with their same class.

Next, in the Sheet where you want to list everyone in Class A, put this formula in Row 1 of the column where you want the last name and drag it down as far as you want.

=VLOOKUP("A" & ROW(),Sheet1!$A$1:$B$9,2,0)

The ROW() function will return 1, then 2, then 3, etc. as you drag it down and VLOOKUP will lookup A1, then A2, then A3, etc.

In rows 1 - 3, you should have:

First 1

First 4

First 9which are the three students in Class A

In another sheet, change the "A" to a "B" and it will lookup B1, B2, B3, etc. returning:

Last 2

Last 6

Last 8Now, if you happen to drag the formula down too far (in this case past Row 3 since there are only 3 occurrences of each class) VLOOKUP will return #N/A since there is no A4 in the list. You can avoid this problem by expanding the formula to check for the #N/A error, returning "nothing" if there is an error:

=IF(ISNA(VLOOKUP("A"&ROW(),Sheet1!$A$1:$B$9,2,0)),"",VLOOKUP("A"&ROW(),Sheet1!$A$1:$B$9,2,0))

Sounds and looks like exactly what I need. It may take me a day or two but I'll try it out and let you know how it goes. The power just came back on after a 36 hour blackout and I have to take care of a few things. Thanks very much for the help.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History