[Solved] Auto populate based off skill.

[2018acway]

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

 

[Lutfor Rahman Shimanto]

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:

​

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

 

[2018acway]

Thank you for all your help.

 

[2018acway]

Can we change it around? Can the formula pick up the employees with the X. X shows they are qualified for the task.

 

[2018acway]

Can we change it around? Can the formula pick up the employees with the X. X shows they are qualified for the task.

Disregard, I was able to change it. Thank you again for your help.

 

[Lutfor Rahman Shimanto]

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,"")), "")

​

Regards
Lutfor Rahman Shimanto
ExcelDemy

 

[Lutfor Rahman Shimanto]

Disregard, I was able to change it. Thank you again for your help.

It is excellent you have figured it out. You are most welcome.

Regards
ExcelDemy

 

[2018acway]

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

 

[Lutfor Rahman Shimanto]

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