pasting cells ignoring blank cells

[lolly150]

I’m using the formula below to check for staff out of date with training (A8 being the staff name which is pasted if the criteria is met):

=IF(AND(Training_Records!N8>12,Training_Records!J8="y",Training_Records!D8=""),Training_Records!A8,"")

This generates a result in each row (some of which are blank as the criteria is not met ) and I now need to select only the cells with data in and paste into a separate sheet, ignoring the blank cells and without gaps.

Please can someone help

 

[Joe4]

How about using Advanced Filters, and Filter the non-blank values to a new location (your other sheet).
See the "Extract Data to Another Sheet" section here: http://www.contextures.com/xladvfilter01.html

 

[lolly150]

Thanks for your reply Joe

How would I do this? I know how to do an advanced filter but what ranges, criteria would I need to us? I would only need to extract the data in column A to a new sheet, ignoring blanks.

Thanks

 

[Joe4]

Take a look here: https://www.mrexcel.com/forum/excel...blank-cells-range-advancedfilter-xls2003.html