Formula to move appropriate data to new sheetClosed

Question

Hey everyone,

I wanted to attach my spreadsheet but no such luck.

Essentially, I have a spreadsheet with people's certifications. I have one column in particular with expiration dates. I ideally would like that anyone who is expired appear on a second sheet within my workbook.

I created a column* at the end of my data with the "IF" function that says "expired", but is there a formula to copy  entire line of data to another page? Or even just two columns with their first and last name?

I can make a mirror image of my first page (basically "IF" THEN every single cell), but then everyone who isn't expired would just be blank on page 2, and I'd have to scroll through nothingness for hundreds of lines. 

Thanks in advance!

* "IF(E:E>TODAY()," ","EXPIRED")"

Kevin@Radstock · Answer

Hi

Assuming the the dates are on Sheet2!E1:E2000 including header and in Sheet2!F1:G2000 are the first and last names, including headers. For excel 2007<, try the following on Sheet1:

=IFERROR(INDEX(Sheet2!F$2:F$2000,SMALL(IF(Sheet2!$E$2:$E$2000<TODAY(),ROW(Sheet2!$E$2:$E$2000)-ROW(Sheet2!$E$1)),ROW(Sheet2!$E1))),"") 

This is an array formula, CTRL + SHIFT + ENTER to commit. Copy across two cells and down.

And for Excel 2010>

=IFERROR(INDEX(Sheet2!F$2:F$2000,AGGREGATE(15,6,ROW(Sheet2!$E$2:$E$2000)-ROW(Sheet2!$E$1)/(Sheet2!$E$2:$E$2000<TODAY()),ROW(Sheet2!$E1))),"") 

Non array formula, just enter, copy across 2 cells and down.

Kevin

Formula to move appropriate data to new sheet

1 response

Similar discussions

Subscribe To Our Newsletter!