|
vlookup help linking data between worksheet
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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200912/1 |
vlookup help linking data between worksheet
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 As and Ps, 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" wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200912/1 . |
vlookup help linking data between worksheet
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 As and Ps, 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 |
vlookup help linking data between worksheet
yes.
It works perfectly. 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 As and Ps, 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 |
| All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com