Rolling up end dates to parent rows, excluding completed items

Nicole_B

I'm having trouble getting my sheet to roll due dates for projects up to parent rows without excluding projects that have already been completed. I've tried enabling dependencies and using a min formula that looks at children rows, but neither options worked. I have no need to track start dates on this particular sheet, only end dates. Ideally I would want the parent row to display the nearest date listed in the children rows without including items that are 100% complete (I'm currently using a % Complete row to track this). Any thoughts?

Paul Newcome

Try using a MIN(COLLECT( type of formula. You can use the COLLECT function to establish that you only want to pull dates that are incomplete and the MIN function will then pull the min for that specific collection of dates.

Nicole_B

Can I still use the CHILDREN function or does it have to be a range of cells when the COLLECT function is used? For some reason I keep getting an error. 

Paul Newcome

Yes. Can you post the actual formula you are using and the error it is throwing?

Nicole_B

Sure, I've tried multiple variations because I haven't been able to figure out what I'm doing wrong, but I'd think it would be something like this:

=MIN(COLLECT(CHILDREN()CHILDREN([% Complete]15),<100))

 

I'm relatively new to smartsheet and don't have much experience with their formulas and functions, so I've been having a hard time troubleshooting. Most of the formulas are returning an unparseable error but one or two have just given me incorrect.

Paul Newcome

You're actually pretty close with yours.

 

=MIN(COLLECT(CHILDREN(), CHILDREN([% Complete]@row),<1))

 

You are just missing a comma between the CHILDREN functions, and SS calculates percentages as decimals where 1 = 100%, .75 = 75%, etc.

 

I also swapped your row reference with @row. This will have the formula looking at whatever row it is resting on. That helps make sure you are looking at the correct set of children when comparing the two.