vlookup help linking data between worksheet

[tabylee via OfficeKB.com]

hi,

I have a master list of students (about 200+ )in one worksheet. On the
succeeding worksheets are the attendance for seminars. We have more than 20
seminars in a year. Because of the large no of attendees per seminar, I
usually type out a list of the students who came for each seminar, so there
will be 20 +attendance worksheets.

Not all students will come every time and there are new ones for each session.
To update on master list, I will sort each sheet by surname, print it out and
type in separate column (date) for each session and typed "P" for present
and "A' absent.

Is there a way to linked the data without printing each attendance worksheet?
A friend suggested that vlookup function will be applicable.
but my problem is some students in master list will be absent and some of
those who came are not yet in master list.

really appreciate your help.

 

[B. R.Ramachandran]

Hi,

Let’s assume that (a) “Sheet 1†is the master list, and the subsequent
sheets (“Sheet 2â€, “Sheet 3†…..) contain the attendance information, and (b)
in each sheet, the names are in column A starting at A2.

In B2 of Sheet1 (i.e., the master list) enter the following formula, and
drag it down the column to the last entry (or even a beyond).

=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet2!$A$2:$A$201,0)),"A","P"))

This formula will populate Column B with “Aâ€s and “Pâ€s, based on the content
of Sheet2 (i.e., the attendance data for the first seminar). It assumes that
there is no more than 200 attendees for that seminar, but I think that should
be ok)

Use similar formulas in C2, D2, …. (for the 2nd, 3rd,…… seminars); modify
the sheet-name in the formulas appropriately for referencing the correct
sheet. For example,

In C2,
=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet3!$A$2:$A$201,0)),"A","P"))

In D2,
=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet4!$A$2:$A$201,0)),"A","P"))


For the scenario where some of the students who attended a seminar are not
in the master list, the following approach may perhaps help.

In B2 of Sheet 2, Sheet3, …… (i.e., seminar 1, seminar 2, ..…) enter the
following formula, and auto-fill down the column

=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet1!$A$2:$A$301,0)),"Add to Master
List",""))

This will indicate attendees who are not yet in the master list, so that you
can manually add their names to the list (The formula assumes that there is
not going to be more than 300 names in the master list; if there is more,
change the range in the formula accordingly).

If this post is helpful, please click "Yes".

Regards,
B. R. Ramachandran

 

[tabylee via OfficeKB.com]

Hi

sorry. I thought no one reply to my post. I just seen your reply tonight.

I will try it out tomorrow and let you know the result.
In case I have any problem, I will ask for your help.

Many thanks for taking time to help me.

Tabylee

Hi,

Let’s assume that (a) “Sheet 1†is the master list, and the subsequent
sheets (“Sheet 2â€, “Sheet 3†…..) contain the attendance information, and (b)
in each sheet, the names are in column A starting at A2.

In B2 of Sheet1 (i.e., the master list) enter the following formula, and
drag it down the column to the last entry (or even a beyond).

=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet2!$A$2:$A$201,0)),"A","P"))

This formula will populate Column B with “Aâ€s and “Pâ€s, based on the content
of Sheet2 (i.e., the attendance data for the first seminar). It assumes that
there is no more than 200 attendees for that seminar, but I think that should
be ok)

Use similar formulas in C2, D2, …. (for the 2nd, 3rd,…… seminars); modify
the sheet-name in the formulas appropriately for referencing the correct
sheet. For example,

In C2,
=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet3!$A$2:$A$201,0)),"A","P"))

In D2,
=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet4!$A$2:$A$201,0)),"A","P"))

For the scenario where some of the students who attended a seminar are not
in the master list, the following approach may perhaps help.

In B2 of Sheet 2, Sheet3, …… (i.e., seminar 1, seminar 2, ..…) enter the
following formula, and auto-fill down the column

=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet1!$A$2:$A$301,0)),"Add to Master
List",""))

This will indicate attendees who are not yet in the master list, so that you
can manually add their names to the list (The formula assumes that there is
not going to be more than 300 names in the master list; if there is more,
change the range in the formula accordingly).

If this post is helpful, please click "Yes".

Regards,
B. R. Ramachandran

hi,

[quoted text clipped - 15 lines]

really appreciate your help.

 

[tabylee via OfficeKB.com]

yes.

It works perfectly.

Hi,

Let’s assume that (a) “Sheet 1†is the master list, and the subsequent
sheets (“Sheet 2â€, “Sheet 3†…..) contain the attendance information, and (b)
in each sheet, the names are in column A starting at A2.

In B2 of Sheet1 (i.e., the master list) enter the following formula, and
drag it down the column to the last entry (or even a beyond).

=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet2!$A$2:$A$201,0)),"A","P"))

This formula will populate Column B with “Aâ€s and “Pâ€s, based on the content
of Sheet2 (i.e., the attendance data for the first seminar). It assumes that
there is no more than 200 attendees for that seminar, but I think that should
be ok)

Use similar formulas in C2, D2, …. (for the 2nd, 3rd,…… seminars); modify
the sheet-name in the formulas appropriately for referencing the correct
sheet. For example,

In C2,
=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet3!$A$2:$A$201,0)),"A","P"))

In D2,
=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet4!$A$2:$A$201,0)),"A","P"))

For the scenario where some of the students who attended a seminar are not
in the master list, the following approach may perhaps help.

In B2 of Sheet 2, Sheet3, …… (i.e., seminar 1, seminar 2, ..…) enter the
following formula, and auto-fill down the column

=IF($A2="","",IF(ISERROR(MATCH($A2,Sheet1!$A$2:$A$301,0)),"Add to Master
List",""))

This will indicate attendees who are not yet in the master list, so that you
can manually add their names to the list (The formula assumes that there is
not going to be more than 300 names in the master list; if there is more,
change the range in the formula accordingly).

If this post is helpful, please click "Yes".

Regards,
B. R. Ramachandran

hi,

[quoted text clipped - 15 lines]

really appreciate your help.