I have 2 sheets: Data and Terminated Employees, respectively.

IN DATA SHEET:
_Column_A_-----------------------------------------_Column_E_
A1:Employees (heading)---------------------------E1:DOT (date of
termination)
A2 and on: actual names of employees-------------E2 and on: dates

In column E, if the employee was NOT terminated, then the cell is left
blank. Otherwise, it has the date of their termination.

WHAT I'M LOOKING FOR:
In the Termination sheet, column B, I want a formula to return the
names of the employees who were terminated.

I get this far:
IF(Data!$E$2:$E$500<\"\",Data!$A$2:$A$500,.....
and I can't think of what to do for the FALSE value of the IF
statement. I did try this:

{IF(Data!$E$2:$E$500<\"\",Data!$A$2:$A$500,INDEX( Data!$A$2:$A$500,ROW(INDIRECT(\"Data!$A$2:$A$500\" ,ROWS($B$32:$B32)))))}
Copied from B32 on down.

Which if the value is false (i.e. the employee is not terminated), then
it will return the next row. The problem is the ARRAY itself will not
skip that row with the FALSE value. This ends up giving me 2 of the
same values (2 of the same employees, when there should only be one of
them).

Any suggestions?


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=395491