JOIN/COLLECT combined with 2 IF statements

Barbara Witt

Good eye, @KDM there was an extra space. Now, both of them say INVALID OPERATION.

If this does not work, maybe I could create a helper column to bring over the date, thus I don't have to go out to the other sheet and get it.....I would make it easier without the helper column.

Any other ideas that you may have?

Thanks, Barbara

Kelly Moore

if you remove the whole IF clause for now, does the first JOIN formula work - the one without any dates

Barbara Witt

Yes, @KDM, this portion works:

JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

It also works with adding IF: =IF([Project Number]@row <> "", JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

But once I add the COUNTIF, then I get the INVALID OPERATION.

Kelly Moore

Looking at your data set for the specific project number the formula is testing - is there any dates that are in the past? I'm wondering if the error is just associated with this one row because it has no past dates?

Barbara Witt

Good thinking, @KDM, I added an install date in the past and one in the future and no install date - same results.

I know this must be frustrating for you, too.

Maybe we should just pick the date up from the same spreadsheet via a helper column and then JOIN/COLLECT from the other sheet.

Thoughts?

Kelly Moore

we can do that. An INDEX/MATCH should be able to pull it in for you. I'm also agreeable to zoom if needed

Kelly Moore

Add this to the TODAY() part

@cell<=DATE(TODAY())

Barbara Witt

Mhm, I get an INCORRENT ARGUMENT on either scenarios.

Like this?

=IF([Project Number]@row <> "", JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Range Install Date}, @cell = <DATE(TODAY()), {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

Then I thought I maybe try TODAY=>@cell({DAX Ambient Inventory Range Install Date}) - but no luck. I tried to mirror the example from the formula spreadsheet.

Kelly Moore

remove the @cell in front of the =<TODAY()

I got that to work in my test sheet

=IF([Project Number]@row <> "", JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Range Install Date}, =<TODAY(), {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

Barbara Witt

@KDM , I am giving up. I know you have been working hard on this one for several days now, so have I. It will not work for me.

=IF([Project Number]@row <>"", JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Range Install Date}, =<TODAY(), {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

Let's go with plan B and use a helper column, which brings the date onto the spreadsheet and we only have to go out to the inventory sheet for the locations.

I know its frustrating.....

Kelly Moore

Sounds like a plan. Are you comfortable setting up the INDEX/Match to get the date. I think it's a Match and not a Collect. Let me know if you need help.

Kelly Moore

And because it's hard for me to let a puzzle go, I tried one more thing in my test sheet.

=JOIN(COLLECT({Test Sheet Locations}, {Test Sheet Date}, AND(@cell <= TODAY(), ISDATE(@cell)), {Test Sheet Project Number}, [Project Number]@row), ", ")

it worked for me.

Barbara Witt

You are too funny, Kelly @KDM. I want to figure this out, too....

I know this formula is working (it just does not consider a date in the past):

=IF([Project Number]@row <> "", JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

I tried to replace the empty project number criteria, with the date criteria, but it does not like it:

=IF({DAX Ambient Inventory Range Install Date} @cell < TODAY(), JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), ", "))

Now, lets try without the IF:

=JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Project #}, [Project Number]@row), ", ") - it's working, but it picks up locations for projects, that have no project number

Now, let's try with just one Date criteria:

=JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Range Install Date}, @cell = <TODAY(), {DAX Ambient Inventory Project #}, [Project Number]@row), ", ") - INVALID

With the other Date criteria:

=JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Range Install Date}, ISDATE(@cell), {DAX Ambient Inventory Project #}, [Project Number]@row), ", ") - yes, it's working

Let's try together with AND:

=JOIN(COLLECT({DAX Ambient Inventory Install Location}, {DAX Ambient Inventory Range Install Date}, AND(ISDATE(@cell), @cell <= TODAY()), {DAX Ambient Inventory Project #}, [Project Number]@row), ", ") - yes, it's working.

Maybe the date criteria was not working, because I had the <= the other way around.

Bottom line, Kelly, your endurance is phenomenal and your dedication is unlimited. Thank you, thank you, thank you.

Sincerely, Barbara

Kelly Moore

Man. I should have caught that for you. Yes the less than equal in wrong order would have done it. The equal sign is always last

I’m glad we got it working