# Formula to move appropriate data to new sheet

Closed
shandiana Posts 1 Registration date Friday November 15, 2013 Status Member Last seen November 15, 2013 - Nov 15, 2013 at 08:39 PM
Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 - Nov 16, 2013 at 02:25 AM
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.

* "IF(E:E>TODAY()," ","EXPIRED")"

## 1 response

Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Nov 16, 2013 at 02:25 AM
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