Summary field = date selected from project plan

SueinSpain

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

我有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

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 bymarking 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!

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

=如果(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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community bymarking 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!

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