Sum(Children())

Nigel Michell

@David Joyeuse

Thanks so much for your input - I am out of the office for the next couple of days but I look forward to reviewing your comments.

Thank you again.

Nigel

Nigel Michell

@David Joyeuse

Thanks for the feedback it was really useful - as the template is work in progress, I have not locked any columns but I will do so once it is finalised.

Can you explain more about Row 4B that you added in relation to basing the conditional formatting on it and also what the =count(ancestors())+1 does.

I like your formula for 'Calculated Status' however I cannot get it to work - it indicates #UNPARSEABLE - not sure why as I have checked the column names and brackets and they all seem fine.

Thanks again for your input.

Nigel

Nigel Michell

@David Joyeuse

Would you be able to comment on my questions above regarding your review of our Template?

Many thanks

Nigel

David Joyeuse

Hi @Nigel Michell

Sorry for the delay, I've been a little bit too busy to come back to you.

Row 4B, to me, is nothing more than an helper column you hide. I like to have this row which can help greatly in card view, if you use it . It's also great to make it easier to differentiate parents from childrens on the grid with conditional formatting as you can apply a specific line color, font, character hieght, and so on based on this column. It really helps having a cleaner sheet to work with.

Now regarding the calculated status column:

=IF([Time Spent (Hours)]@row > 0, IF([% Comp]@row = 1, “Complete”, IF([% Comp]@row = 0, “Not Started”, “In Progress”)), “Not Started”)

As, I think @Genevieve P pointed it out on another thread, you'll note that quotes here are a little bit curved as the formula is being paste from Word. They should be straight.

Try copy/paste this formula:

=IF([Time Spent (Hours)]@row>0; IF([% Comp]@row = 1, "Complete", IF([% Comp]@row = 0, "Not Started", "In Progress")), "Not Started")

That one is a little tricky, but the problem comes from the quotes.

Nigel Michell

Thanks @David Joyeuse

I copied the formula and then I manually changed the quote marks to ensure that they were correct but Smartsheet still says the formula is Unparseable.

This is what I pasted:

=IF([Time Spent (Hours)]@row>0; IF([% Comp]@row = 1, "Complete", IF([% Comp]@row = 0, "Not Started", "In Progress")), "Not Started")

Looking at this now I can see what the problem is - there is a semi colon rather than comma after the first 'If'!

It now works!

Many thanks again for your time

David Joyeuse

Ah sorry, old french typing habits :P

Nigel Michell

@David Joyeuse

Yes - I work in Quebec and I am always getting confused between ',' and ';'!