| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| linking data from one worksheet to another | Excel Worksheet Functions | |||
| Linking worksheet data | Links and Linking in Excel | |||
| Data Validation vs VLOOKUP - Linking to data in a seperate file | Excel Worksheet Functions | |||
| Linking worksheet data | Excel Worksheet Functions | |||
| can I name an excel worksheet tab by linking to worksheet data? | Excel Worksheet Functions | |||
Threaded Mode
