[Solved] Auto populate based off skill.

2018acway

New member
Good day,

I have several tasks on Master List based on days. I would like a formula to populate an instructor from sheet1 that has the skills to perform the task
 

Attachments

  • Training Class Schedule.xlsx
    961.9 KB · Views: 1
Good day,

I have several tasks on Master List based on days. I would like a formula to populate an instructor from sheet1 that has the skills to perform the task
Dear Acway

Thanks for reaching out and sharing your problem with such clarity. You want a formula to populate instructors in the Master List sheet based on the skills to perform the task from Sheet2.

I have reviewed your attached dataset and found many inconsistencies. However, I have to change it a bit for testing purposes. Finally, I developed a complex formula to populate the intended skilled instructors using IFNA, TEXTJOIN, IF, INDEX, and MATCH functions.

SOLUTION Overview:

populate instructors in the Master List sheet based on the skills to perform the task from She...gif

Follow these steps:
  1. Select cell G3.
  2. Insert the following formula:
    =IFNA(TEXTJOIN("/", TRUE,IF(INDEX(Sheet2!$D$2:$T$47,,MATCH('Master List'!$A3,Sheet2!$D$1:$T$1,0))<>"X",Sheet2!$B$2:$B$47,"")), "")
  3. Drag the Fill Handle icon to copy the formula down.

I assume your dataset, especially the fields within Sheet2, will increase in future. If so, you must modify the provided formula accordingly. I have attached the solution workbook for better understanding; good luck.

Regards
Lutfor Rahman Shimanto
ExcelDemy
 

Attachments

  • Acway (SOLVED).xlsx
    962.1 KB · Views: 1
Can we change it around? Can the formula pick up the employees with the X. X shows they are qualified for the task.
Dear Of course. You can easily do it by modifying the formula. All you need to do is to replace the <> sign with the = sign.

Excel Formula:
=IFNA(TEXTJOIN("/", TRUE,IF(INDEX(Sheet2!$D$2:$T$47,,MATCH('Master List'!$A3,Sheet2!$D$1:$T$1,0))="X",Sheet2!$B$2:$B$47,"")), "")

Auto Populate Based Off Skill.png

Regards
Lutfor Rahman Shimanto
ExcelDemy
 
It is excellent you have figured it out. You are most welcome.

Regards
ExcelDemy
Good day, I have one more to ask. On the same attachment. If you unhide the tabs, you'll see 3 more tabs. Is there a way to have the tasks & dates populate onto the Master tab and assign it to an instructor? I currently have to manually input that data
 
Good day, I have one more to ask. On the same attachment. If you unhide the tabs, you'll see 3 more tabs. Is there a way to have the tasks & dates populate onto the Master tab and assign it to an instructor? I currently have to manually input that data
Dear, Thanks for your patience! We have reviewed your dataset and found many data inconsistencies, so we are having difficulties coming up with an idea. Again, as the problem seems like a new one, it would be better if you create a new thread, explain the requirements in more detail, and provide consistent data that will help us test when we have finished developing formulas.

Regards
Lutfor Rahman Shimanto
ExcelDemy
 
Last edited by a moderator:

Online statistics

Members online
0
Guests online
14
Total visitors
14

Forum statistics

Threads
318
Messages
1,408
Members
583
Latest member
mibr fan token
Top