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

B. R.Ramachandran wrote:
Hi,

Lets 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.

--
Message posted via http://www.officekb.com