Copy Cells into a separate list if certain criteria are met

[TrojanHorse8806]

Hello,

I am in need of assistance with copying cells into a list on a separate page based on certain Criteria.

The workbook attached is for tracking training and identifying Trainers. The employee names are listed horizontally in row 4 of each page. Each page is representative of different shifts and I'm looking to compile trainer lists vertically on page titled "Trainers." I will ultimately repeat this for multiple departments.

The criteria for the employee to be identified as a trainer is a "YES" in all cells 42:45 of their corresponding column. There is also a reference "YES" in the sheet titled Legend (Cell R16) if needed.

I currently have the conditional formatting for the employee name to change to purple with double underline if all four cells (42:45) are a yes.

I cant seem to find how to manipulate the formulas/vba to copy it based on the criteria and place it appropriately. I have manually added the names that would qualify on the Trainer sheet as an example.

Thanks for any advice! PS I'll be posting this same workbook in another thread for a conditional formatting question.

[AliGW]

'Copy' in your title and 'move' in your post - which is it? They are not the same thing at all. The latter would need VBA. Please confirm, and if the latter, I'll move this thread to the correct forum section.

[TrojanHorse8806]

My apologies! first time poster and not very well versed in excel lol. It is copy that I am requesting- Thank you so much!

[GeoffW283]

Welcome to the forum

First you need to rename your headers on the training sheet row 3 to exactly match the worksheet names:
A3: Dept 1st B3: Dept 2nd 4th C3: Dept 3rd

Please then try the following in cell A4 of the Training worksheet.
This is an array formula, but as you have O-365 that shouldn't matter to you.

Copy across to C4 then copy A4:C4 down a few rows.

Please Login or Register  to view this content.

This gives your expected results for validated trainers.

Updated workbook is attached.

Let us know if this works for you.

[GeoffW283]

Glad it worked for you. Thanks for the added reputation.

If you are all set then please mark the thread as solved via the thread tools menu at the top of this tread.