copy cells based on value of another cell

June 26, 2009 at 06:12:17
Specs: windows XP home
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,

See More: copy cells based on value of another cell

Report •

June 26, 2009 at 20:02:16
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 lookup functions, 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    A

Here'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    A

Now 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 9

which 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 8

Now, 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:


Report •

June 28, 2009 at 15:24:37
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.

Report •

Related Solutions

Ask Question