IF, AND statement with 2 criteria to return text from reference sheet

kim b

Need some help..

I am referencing another sheet for all of my references

I need the formula to return the name of the project if the deadline date is within 210 days from today's date and the date paid does not have a date.

=IF(AND({Deadline Range 1} >= TODAY(210), {Date Paid Range 2} <> ""), {Project Range 3}, 0)

Kelly Drake

Try this:

= IF(AND({Deadline Range 1} <= TODAY()+210, ISBLANK({Date Paid Range 2}), {Project Range 3}, 0)

kim b

It returned #INCORRECT ARGUEMENT.

=IF(AND({Deadline Range 1} <= TODAY() + 210, ISBLANK({Date Paid Range 2}), {Project Range 3}, 0))

Genevieve P.

Hi @kim b

In this instance, I would recommend using a JOIN(COLLECT formula. This way if there's more than one Project that fits your criteria you'll see the names of all of them, versus just the first match.

Try this:

=JOIN(COLLECT({Project Range 3}, {Deadline Range 1}, @cell <= TODAY(210), {Date Paid Range 2}, ""), " / ")

If there are many projects that fit this criteria, you may want to think about creating a Report instead, so you can view more details about each Project. Let me know if you need help building this out!

Cheers,

Genevieve

Bassam Khalil

Hi @kim b 

Hope you are fine, please try the following formula:

=JOIN(COLLECT({Project Range 3}, {Date Paid Range 2}, ISBLANK(@cell), {Deadline Range 1}, TODAY(-210) <= @cell), " - ")

or @Genevieve P formula ( she posted it before me but i didn't notice until i posted mine )

kim b

Hi, thanks for getting back to me.

Genevieve's returned an #INCORRECT ARGUMENT and the one you provided gave me a return of the project name, but it also included all of the other project names in the same cell.

Genevieve P.

Hi @kim b

Yes, the JOIN function will join together all Projects that meet your criteria. Do you have a different criteria to indicate which project name you want to bring back? I would presume there are multiple that have a deadline within 210 days. Would a Report be better?

kim b

Hi Genevieve,

I tried a report, but it will not allow me to use it as the data source for a metric widget, which is my ultimate goal.

Maybe I'm not explaining myself clearly. I am looking for a way to pull a project name (column A) into another sheet if the deadline for that specific project is within 210 days from today's date and the date paid column is blank.

thanks,

Genevieve P.

Hi @kim b

You're right, you would want to embed the Report as a Report Widget instead of a Metric Widget.

When you note "a project name", how are you finding this one project?

It sounds like when you used @Bassam Khalil's formula it pulled in a number of Projects into one cell, right? This is because there is more than one project that is within 210 days from Today's Date with a Date Paid cell showing as blank.

What other criteria can help filter this down to only show the one project you're looking for? Or do you want to see all of the project names that the formula pulled in, but separately (versus in the same cell)?

kim b

thanks for the quick response..

I want the project names to be listed separately with their corresponding deadline date next to it.

So if the project column A meets the criteria I want that name to be pulled into another sheet and in the column next to it, the deadline date. My goal is to have all Projects with upcoming deadlines shown on the dashboard.

Genevieve P.

Hi @kim b

Thanks for clarifying! There currently isn't a way to do this with a formula where it will parse out the project names down rows in the way you're describing. The way to do this would be to create a Report and use a Report Widget (see here) on the Dashboard instead.

The formula suggested above is the other alternative, but as you saw, this will JOIN together all the Project Names into one cell if there's more than one.

A Report Widget will identify all Projects with upcoming deadlines, and easily display any other associated information (such as the Date column you mentioned). This can then be shown in the Dashboard. You mentioned trying a Report before, have you tested with the Report Widget instead of a Metric Widget?

kim b

Well, that's unfortunate, but I appreciate you taking the time in answering my questions.

Yes, I have a report with this data and will do it that way.

thanks