Any idea what I could be doing wrong?

Guiga S

Hi all,

I need to create a formula that will collect info from a different (original project) sheet and display the right data on the cell of a new sheet (used to build a report) - that way things stay updated as the project progresses.

So, a couple of things need to be taken into account, the info displayed needs to meet these criteria:

- {Project Plan: XXX Range 34} = This range represents the task names in the original sheet (and should appear as the "result" of this formula)

- {Project Plan: XXX Range 35} = has to be true (this is a checkbox column and the flag needs to be checked)

- {Project Plan: XXX Range 36} = has to be anything but Completed (this is a status column and needs to pick up any task that is flagged and not completed [in progress, not started])

- {Project Plan: XXX Range 37} = this is a final date column, and needs to "pick up" the closest date to today's date, prioritizing a past date over a future date.

So basically, whichever ONE task checks all the boxes is the task I need to appear in the cell where this formula is being applied.

I got stuck here:

=IFERROR(INDEX({Project Plan: XXX Range 34}, MATCH(IFERROR(MAX(COLLECT({Project Plan: XXX Range 37}, AND({Project Plan: XXX Range 35} = true, {Project Plan: XXX Range 36} <> "Completed", {Project Plan: XXX Range 37} <= TODAY()))), MIN(COLLECT({Project Plan: XXX Range 37}, AND({Project Plan: XXX Range 35} = true, {Project Plan: XXX Range 36} <> "Completed", {Project Plan: XXX Range 37} > TODAY())))), {Project Plan: XXX Range 37}, 0)), "")

But this is coming back as an incorrect argument set. Does anyone have any ideas on how to make this work?

I appreciate any and all help!! Thanks!! :)

Paul.Woodward

Hello my friend.

I am wondering if the use of AND instead of commas is throwing things off. Try this version?

=IFERROR(INDEX({Project Plan: XXX Range 34}, MATCH(IFERROR(MAX(COLLECT({Project Plan: XXX Range 37}, {Project Plan: XXX Range 35} = true, {Project Plan: XXX Range 36} <> "Completed", {Project Plan: XXX Range 37} <= TODAY())), MIN(COLLECT({Project Plan: XXX Range 37}, {Project Plan: XXX Range 35} = true, {Project Plan: XXX Range 36} <> "Completed", {Project Plan: XXX Range 37} > TODAY()))), {Project Plan: XXX Range 37}, 0)), "")

Check if {Project Plan: XXX Range 34}, {Project Plan: XXX Range 35}, {Project Plan: XXX Range 36}, and {Project Plan: XXX Range 37} are correctly assigned and ensure that the date conditions (<= TODAY() and > TODAY()) are logically structured to meet your needs.

Guiga S

This is great - the formula works!!
But it is retuning a blank ("") value, when in fact I needed it to return whatever content is in the cell that it all applies to in {Project Plan: XXX Range 34}. Any suggestions?