Welcome!

Rolling up end dates to parent rows, excluding completed items

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?

Find more posts tagged with

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Update Automations
Hi All, We have several update automations on various solutions - and have worked very well for getting info from people that dont have smartsheet access - how ever in Sept we switched over to SSO and MFA and activated the Safe Share - We have now run into an issue that when our update automations trigger and people within…
Public links to sheets in automated emails?
I am trying to send non-Smartsheets users information on forms. I know I can create a publicly accessible and editable link for the overall sheet/form, however, when I set up automated emails with links to the forms, the links to sheets/rows still require a login. Is there any way to get around it so that emails sent…
Generating documents from row data
This discussion was created from comments split from: Generate documents from row data.