Formula to move appropriate data to new sheet

[Closed]
Report
Posts
1
Registration date
Friday November 15, 2013
Status
Member
Last seen
November 15, 2013
-
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
-
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")"

1 reply

Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
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