Summary field = date selected from project plan

So I have a standard project plan with timeline / gantt chart.

Start date = [Start] and end date = [Finish]

I have several mini projects in my smartsheet so have used the level calculation to detect level = 0 for start of my mini projects with levels 1 onwards for the children tasks

I have successfully written the formual to count how many tasks have been not started, in progress, complete for each project identified by the Project Element field e.g.

=COUNTIFS(Status:Status, "In Progress", [Task Name]:[Task Name], <>"", Level:Level, >1, [Project Element]:[Project Element], ="Control Tower Transport")

However - I now want to pick up the Start field value where the Level =0 (or <1 whichever) and the Project Element = "Control Tower Transport]

So I tried this but get unparsable

=IF(Level=0, IF([Project Element]=”Control Tower Transport”],Start))

It works if I add the row number but that changes as I add more lines etc.

I think I am missing something obvious but just need some help please

Many thanks as always for the anticipated help & support

Cheers

Sue

Find more posts tagged with

Formulas

Accepted answers

Andrée Starå

Hi @SueinSpain

I hope you're well and safe!

I'd recommend selecting the actual cell directly for each mini-project. The reference will update itself when there are new rows are added.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

SolutionSal

Hi Sue! if the formula you provided is an exact copy of what you have in your cell then I see the error. You have an extra close bracket in your logical_expression of the second if statement. So this:

=IF(Level=0, IF([Project Element]=”Control Tower Transport”],Start))

Should look like this:

=IF(Level=0, IF([Project Element]=”Control Tower Transport”,Start))

Hope this helps!

SueinSpain

Oh wow - sometimes you just cannot see it. I'll give it a go and thanks so much for answering

SueinSpain

Sadly it still says unparsable.

I have also tried making Level="0" or <1 but doesn't work :(

Thanks for trying though

SueinSpain

So just to further add.

I have corrected the extra bracket which somehow made it's way in there but it still doesn't work.

I have then tried the formula but with the row number and that does work

=IF(Level34 = 0, IF([Project Element]34 = "Control Tower Transport", Start34))

So my problem appears to be with the row numbers removed..

I have 3 x level 0 rows and just want to pick out each one spearately for a summary field - is the answer that I have to have the row number and, if this is the case, will it be updated automatically as more rows are added.

Andrée Starå

Hi @SueinSpain

I hope you're well and safe!

I'd recommend selecting the actual cell directly for each mini-project. The reference will update itself when there are new rows are added.

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

SueinSpain

Hi Andree

Thanks for the reply. I suspected it was only going to work with cell reference after trying lots of things and yes this does work so thank you. I think self-doubt creeps in after a while.

I am well thank you and hope you are too.

Have a lovely rest of your week

kindest regards

Sue

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.