Hi All,
can i ask is there a way or any formula where I can return multiple match values from two different workbook?
The formula i used only works for same sheet but when i tried to return values from different workbook it cannot work. The data should be recording in my training record as shown in the image attached.
Master List:
The master list should record in this training record based on candidate name.
Training Record:
can i ask is there a way or any formula where I can return multiple match values from two different workbook?
The formula i used only works for same sheet but when i tried to return values from different workbook it cannot work. The data should be recording in my training record as shown in the image attached.
Master List:
| Master List.xlsx | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | H | I | J | K | |||||||
| 7 | Summary of Training Course - Year 2021 | ||||||||||||
| 8 | |||||||||||||
| 9 | Apps for Division: | ||||||||||||
| 10 | Candidate Name | Dept | Title | Remark | 7 | Sangilikaruppan Selvam | |||||||
| 16 | S. Karuppiah | Production | Coretrade Renewal reinforced concrete | 7 deaths from workplace accidents in Feb 2021 | |||||||||
| 18 | K.Ramesh | Production | Coretrade Renewal reinforced concrete | ||||||||||
| 19 | Hossain Ellius | Production | Coretrade Renewal reinforced concrete | ||||||||||
| 28 | Sangilikaruppan Karuppiah | Production | Procedure Reinforced Concrete Works Via Zoom | ||||||||||
| 29 | Karuppiah Ramesh | Production | Procedure Reinforced Concrete Works Via Zoom | ||||||||||
| 30 | Hossain Elius | Production | Procedure Reinforced Concrete Works Via Zoom | ||||||||||
| 31 | Kolappa Pillai Mani Kandan | Production | Rebar Hazards | ||||||||||
| 32 | Sangilikaruppan Karuppiah | Production | Rebar Hazards | ||||||||||
| 33 | Karuppiah Ramesh | Production | Rebar Hazards | ||||||||||
| 34 | Sangilikaruppan Selvam | Production | Rebar Hazards | ||||||||||
| 35 | Karuppiah Sivalingam | Production | Rebar Hazards | ||||||||||
2021 Summary (All Dept.) | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I10 | I10 | =COUNTIF($A$11:$A$73,$J$10) |
| J16,J28:J29,J18:J19 | J16 | =IF(ROWS($I$11:I16)<=$I$10,INDEX(TableDiv[[Title ]],AGGREGATE(15,3,(TableDiv[[Candidate Name ]]=$J$10)/(TableDiv[[Candidate Name ]]=$J$10)*(ROW(TableDiv[[Candidate Name ]])-ROW(TableDiv[[#Headers],[Candidate Name ]])),ROWS($I$11:I16))),"") |
The master list should record in this training record based on candidate name.
Training Record:
| Employee Training Record - 2021 (Production).xls | |||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | IP-TRG-01-01 Rev 08 | ||||||||||||||||
| 2 | EXCEL PRECAST PTE LTD | ||||||||||||||||
| 3 | EMPLOYEE TRAINING RECORD | ||||||||||||||||
| 4 | 2021 | ||||||||||||||||
| 5 | Employee Name | : | Sangilikaruppan Selvam (W204) | Date Joined | : | 6/7/2004 | |||||||||||
| 6 | |||||||||||||||||
| 7 | Department | : | Production | Designation | Worker | ||||||||||||
| 8 | |||||||||||||||||
| 9 | |||||||||||||||||
| 10 | S/No. | TRAINING / | DATE / | TRAINER / | Fees | REMARKS | |||||||||||
| 11 | COURSE NAME | PERIOD | ORGANISATION | (IF ANY) | |||||||||||||
| 12 | 1 | ||||||||||||||||
| 13 | |||||||||||||||||
| 14 | |||||||||||||||||
W204 | |||||||||||||||||
