Need help with complex IF function to return values on different sheet

[mccormickj]

I have a list of tasks/issues to be dealt with, along with their start and end dates. Simple enough, like this, generically:

Issue_______Start___Close
Problem 1___8/22
Problem 2___8/23____9/1
Problem 3___8/31
Problem 4___9/1_____9/1
Problem 5___9/1

On my report summary sheet, I would like to display the name of said issue(s) only if the end date is not yet complete (cell is empty).

=IF('Issue Tracker'!F6="",'Issue Tracker'!C6,"") -- where F6 is the cell of the Close date, and C6 is the cell that contains the problem description -- works fine, except that the output on the report is relative to the row positions of the open/incomplete tasks. In other words, if Task 1 is open, but Task 2 is closed, then Task 3 is open, it will display the task names, but there is an empty row between the tasks on the report worksheet (see below):

Open Issues
Problem 1
[blank row]
Problem 3
Problem 4
[blank row]
Problem 6


How can I get the IF formula to find all of the open tasks within a range and list them sequentially, rather than using the position-relative fill handle technique? I tried referencing the array of the columns, but get a VALUE error.

Many thanks for assistance...

[NBVC]

Assuming your data is in A1:C10

Try something like:

=IFERROR(INDEX($A$2:$A$10,SMALL(IF($C$2:$C$10="",ROW($C$2:$C$10)-ROW($C$2)+1),ROWS($C$2:$C2))),"")

adjust ranges to suit, and confirm with CTRL+SHIFT+ENTER, not just ENTER and then copy down.

[mccormickj]

Hmmm... no, got nothing with this, just merged a couple of cells together. No output whatsoever. Even threw together some dummy data for cells A1:C10 to test if I had messed up the references.

Thanks for putting some thought into this for me, though. It was worked out with a helper column.

[NBVC]

I think you just didn't confirm the formula entry with CTRL+SHIFT+ENTER as instructed.

See attached.

If you got another solution from elsewhere, please post the link...