JOIN/COLLECT combined with 2 IF statements

Barbara Witt

Hello there,

This is a formula, which I struggle with (the portion bolded):

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

If project number is > 1 (or not blank) AND date is in the past, then JOIN/COLLECT. I know the JOIN/COLLECT portion is working with the 1st IF. But I struggle when I added the second IF (date in the past). Plus, how do I change project number 1 to NOT(ISBLANK)?

Thanks for helping me with both scenarios.

Best regards, Barbara

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.

Kelly Moore

Hey Barbara

IFs can't be used directly with cross sheet references. You're comparing the entire cross sheet column to TODAY. Is there a specific cell criteria in the {DAX Ambient Inventory Actual Install Date} that you wish to compare TODAY to? Project Number matches @row? COUNTIFS can be used to test if criteria are true when values are greater than zero.

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

Also, is there back date to check against? At some point all dates are less than Today since time marches forward

Kelly

Barbara Witt

You are right, @KDM . I want to compare the Actual Install Date to TODAY (in the past) if there is a project number @row.

Only bring over the Install Location to the field where the Project Number matches if the Install Date is in the past. The Install Location also houses the warehouse location. If there is a future install date, then the warehouse location would come over, but I don't want that. Thus I am looking for the install date in the past. Once passed, match up the Project Numbers and bring the Install Location over.

Does that make sense?

I could not get the above formula to work. Here is the screen print.

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

Maybe we should simply it and just look at an Install Date in the past, then compare project number to project number and dump in the install location. It sounds easy, but scripting this in SmartSheet language is hard.

Thanks again for your continued assistance. I could not do this without you. After this, it's time for me to study SmartSheet, because I want to learn it. For now, I have a due date, which I need to meet.

Barbara

Kelly Moore

Hey

In the formula above, there are parentheses missing. We have to make sure the COUNTIFS is closed so it can be compared to zero. Then, the AND function has to close. The JOIN can be open.

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

Didn't you want a delimiter between your joined words?

Let me know if this works

Kelly

Barbara Witt

Hi there, @KDM , yes, I am the one with the delimiter in the same formula, too and we got that worked out. Yipee!

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

Mhm, it gives me an INVALID. I think I closed the AND and the COUNTIFS.

I actually think that I will need this formula in a few other places, too (date in the past), because the technical folks are allocating devices by populating the ship/install dates, even if they are future dates. I don't want the data to flow over (number of devices shipped/installed, location), until the devices has been shipped/is installed. Once we get this to work, then I'll use the same logic in 6 other places.

In addition, could you help me understand why we need >0?

IF project number is not blank: IF [Project Number]@row <> ""

AND Date for the project number is in the past where the project number matches (COUNTIF): COUNTIFS({DAX Ambient Inventory Range Install Date}, @cell<TODAY(), {DAX Ambient Inventory Project #},[Project Number]@row)

>0 Why do I have the > 0 in there?

THEN bring over the location for that project number (JOIN/COLLECT)

I am going to go back into all my conversations and pick out the formulas and explanations, for my formula spreadsheet, so I can more easily duplicate the logic in the future.

Thanks so much. Best regards, Barbara

Kelly Moore

First, the > zero

We're in an IF statement so we must ask IF what. A COUNTIFS will return a value but, unless we compare it to something, it is only a value. What we really want to know is if there is any kind of a match on the other sheet to the criteria that is within the countifs. Using COUNTIFS, if there is any match at all, the Countif will have a value of 1 or more because it's purpose is to count those matches. Thus we are using this capability to check for matches or non-null cells in the other sheet since we cannot just say IF Other sheet is not blank do so-and-so. Plain IFs don't work in cross-referenced sheets. This concept is a biggie in the formula world so if I didn't explain it well, ask me again and I'll try again. Or someone more eloquent in the community will help me out.

Now, getting back to our invalid ?. Invalid what?

We'll start trouble shooting. Here's what I do. I drag my existing formula down at least one row so I have an easy copy of it. It also helps me test whether the problem is just with the one row I happen to be working on. Once you have drug it down 1-2 rows, delete everything after the >0),

edit to this

=IF(AND([Project Number]@row <> "", COUNTIFS({DAX Ambient Inventory Range Install Date}, @cell < TODAY(), {DAX Ambient Inventory Project #}, [Project Number]@row), "Yay", "Boo")

If we get an error, in that same formula row, delete further so that only the COUNTIFS remain. By doing this, we're trying to isolate what might be causing an error. This is exactly how I trouble shoot my own formulas.

Let me know - I should be pretty quick to respond

Barbara Witt

@KDM that makes sense, Kelly. Thanks for the explanation of the formula, too, which helped when I tried to isolate the varies parts of the formula to test them. I hovered over the formula to see where each starts and ends.

All parts are working on their own. The entire formula is working when not true, because the word "false", will come up. However it will not work when it is true, because it is not collecting the locations.

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

This is bizarre. It works on it's own. It works if false, but it does not work if true. How can that be?

Thanks so much, Barbara

Kelly Moore

Barbara,

There is no comparison again in the countifs. Please add the greater than zero back to test.

Kelly Moore

with the greater than zero included - did the IF(AND) work? That was my bad - I backspaced too far in the formula.

Kelly Moore

@Barbara Witt

Were you able to get it to work?

Barbara Witt

@KDM Hi Kelly, I have not forgotten about you.

I will take the formula apart again and check every sequence by itself to see if I can determine where the error is. I'll be in touch later this week.

Thanks so much for helping me.

Best regards, Barbara

Barbara Witt

Hello there, Kelly, @KDM

I am off today, so I have some time to focus on this again.

Here is what I have thus far:

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

Now, instead of excluding the empty rows without project numbers, I want to exclude the rows with a project date in the future (only use rows with project dates in the past). I came up with this formula, but it's unparseable. I even replaced @cell with @row, but it won't do it.

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

Thoughts? Thanks, Barbara

Kelly Moore

Is your IF statement referencing the same sheet or a cross referenced sheet? IF statements can't be used directly for cross references. This is why we use countifs

Kelly Moore

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

Why not add the date criteria as part of the collect?

Barbara Witt

So sorry, @KDM, you told me that before about the IF statement. I need to add this to my formula sheet, so I don't forget about it.

Certainly, the date feature could be part of the IF or part of the COLLECT. I tried both, but neither of them are working out for me.

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

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

Are you able to see the mistake?

Thanks, Barbara

Kelly Moore

Is there an extra space between TODAY and ()?