unexpected behavior with collect formula

L_123

I believe this formula should work, and when I break it apart it seems to work but doesn't when together.

 

Copy from sheet

=JOIN(COLLECT({Range 1}, {Range 2}, IF($[By Shift]$1 = "All", @cell = @cell, @cell = $[By Shift]$1), {Range 3}, IF(ISNUMBER($[Within Last (Days)]$1), NETDAYS(@cell, TODAY()) < $[Within Last (Days)]$1, AND(@cell > $[Date Range]$1, @cell < $[Date Range]$2))), ",")

 

Easier to read/understand version

               ***Start Formula

=JOIN(COLLECT(

               ***Return Range

{Range 1},

              ***Criteria 1 Range

{Range 2},

               ***Criteria 1

IF($[By Shift]$1 = "All", @cell = @cell, @cell = $[By Shift]$1),

               ***Criteria 2 Range

{Range 3},

               ***Criteria 2

IF(ISNUMBER($[Within Last (Days)]$1), NETDAYS(@cell, TODAY()) < $[Within Last (Days)]$1, AND(@cell > $[Date Range]$1, @cell < $[Date Range]$2))), ",")

 

 

 

If I delete criteria 2, the formula works. Or if I delete the @cell = @cell in criteria 1 the formula works as expected. Also the formula works until i change [By Shift]1 to "All"

I wonder if there is a limit to the number of @cell references in a formula, or if I just limited out the power of smartsheet.

It seems like every part of the formula works in my testing until it is all together and referencing "All" and I get a invalid data type error. 

Paul Newcome

IF(ISNUMBER($[Within Last (Days)]$1), NETDAYS(@cell, TODAY()) < $[Within Last (Days)]$1, AND(@cell > $[Date Range]$1, @cell < $[Date Range]$2))), ",")

 

One thing I do see right off is this particular IF statement.

 

You're saying here: If [Within Last (Days)]1 is a number, then input NETDAYS(@cell, TODAY()) < $[Within Last (Days)]$1. Otherwise input AND(@cell > $[Date Range]$1, @cell < $[Date Range]$2)).

 

Your "then" portion is more of a logical statement than an actual value.

NETDAYS(@cell, TODAY()) < $[Within Last (Days)]$1. Otherwise input AND(@cell > $[Date Range]$1, @cell < $[Date Range

Your "else" portion is a similar issue. You are not actually stating a value.

AND(@cell > $[Date Range]$1, @cell < $[Date Range]$2)

 

You also have the same thing going on here:

IF($[By Shift]$1 = "All", @cell = @cell, @cell = $[By Shift]$1)

 

The formula requires a value, but you have entered a logical statement instead.

 

Try playing around with this portion of your formula to see what happens.

L_123

I'm using the if statement to return the criteria for the collect. It worked in my testing, i've posted a link below.

https://app.smartsheet.com/b/publish?EQBCT=108bce1f756c44dcae397e87b19a2a7b

 

Check the formula in the yellow box. It has the expected behavior I want in the equation in this question.

L_123

I figured out a workaround, but I am still frustrated that I don't understand what the difference between this equation and my test formulas. I suppose it might just be that the @cell = @cell reference isn't stable. My shift column only has 3 possible responses, so I put:

 

or(@cell = "1st",@cell = "2nd",@cell = "3rd") instead of @cell = @cell

 

This seems like it would use more computational power and slow down the sheet, but there are only 4 of these on my sheet.

 

I would like to understand what is wrong with the original still though.

Paul Newcome

Well... I was wrong. I've never seen it done that way and was always under the impression that you had to have an actual value in those two places. I'm going to have to do some studying on that mess.

 

I'm pretty sure my brain just broke. Haha. Anyway...

 

I assume you've checked your column types... You said it works until it is all together and referencing "All".

 

Is it possible to put an IFERROR in a few different spots within the formula and designate each one it's own number? If you can do that, theoretically that could tell you where the issue lies.?

L_123

I've tried that. The formula works if I delete the back half with the @cell = @cell reference still there, and the formula works if I leave the back half and just change the @cell = @cell.

 

The back half has nothing to do with the @cell = @cell which is why I am having a difficult time understanding where my error is.

Paul Newcome

There must be an @cell limit as you suggested earlier. That's just weird, but then again... This IS Smartsheet... If you ever find out why it wasn't working or if there is in fact an @cell reference limit, let me know. I don't use it much, but it's still good to know. Glad you were able to figure out a workaround.

L_123

Will do. Thanks for the input. 

 

This is one of the reasons I really like the collect function. It may have some glitches, but it lets you be really flexible in your thinking and formula writing. I think it is the only formula that allows for a conditional range based on different criteria. 

Once you get to that level you are doing some very basic programming with arrays and it opens up a whole new world of analyzing data and compiling sheets within smartsheet.

 

I really do wish I understood this one though. It's going to bug me for a while. Hopefully a dev sees this and can make some sense of it.