Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Help with "Syntax not quite right"

✭✭✭✭✭✭
edited 01/31/25 in Formulas and Functions

Hello!

Looking for help with this formula for returning "Travel Week Start Date".

I want all the children Rows under parent Row 2 to have the value from Row 111 "Start Date" column. I might need an Index/find function, but unsure.

I'm getting an error, as shown below.

=IF(CONTAINS("WASP", ANCESTORS(Task@row)), [Start Date]111, IF(CONTAINS("Compliance", ANCESTORS(Task@row)), [Start Date]343, IF(CONTAINS("Programmatic", ANCESTORS(Task@row)), [Start Date]571, "")))

Best Answers

Answers

  • Community Champion

    Hey @Ami Veltrie

    One cannot use an absolute reference in a column formula, which is causing the syntax error.

    Since you want the Start Date to be in all the rows, I used DESCENDANTS instead of ANCESTORS. All of the rows are DESCENDANTS of the Grandparent however all of the rows are not ANCESTORS of each other.

    Try this

    =IFERROR(IF(OR(CONTAINS("WASP", DESCENDANTS(Task@row)), CONTAINS("Compliance", DESCENDANTS(Task@row)), CONTAINS("Programmatic", DESCENDANTS(Task@row))), INDEX(DESCENDANTS([Start Date]@row), MATCH("Travel Week Start Date", DESCENDANTS(Task@row), 0)), PARENT()), PARENT())

    Does this work for you

    Kelly

  • ✭✭✭✭✭✭

    Hello @Kelly Moore

    Thank you for taking the time to help me out.

    No, that formula doesn't seem to be working. It results in a blank cell.

    Is there anything else I can try?

  • Community Champion

    Hello @Ami Veltrie

    The formula worked in my test sheet. Let’s verify I had my sheet set up correctly.

    On the row(s) with the text “Travel Week Start Date” in the “Task” field, I placed a date in the “Start” field. If there is no date here the returned value is blank. The formula was placed in the [Travel Week Start Date] column as a column formula. All rows in any given Grandparent’s family should return the ‘Start’ date.

    Did I set up my sheet correctly?

    Kelly

  • ✭✭✭✭✭✭

    Hello @Kelly Moore

    Yes, that sounds right. I added your formula to the Travel Week Start Date column (Rows 114 - 118) and then I added the Start Date to Row 111.

    I thought maybe the issue was that Row 1 Task is higher level than the Task we want recognized for this formula, so I outdented Row 2, just to see if that would fix it. It didn't, so I re-indented it.

    Here's what my sheet looks like after adding your formula to Row 118:

  • Community Champion

    Hey @Ami Veltrie

    Place the formula in row 1 and make it a column formula

    Does that work for you?
    Kelly

  • ✭✭✭✭✭✭
    edited 02/03/25

    Hi @Kelly Moore

    Thank you. That worked for a minute, until I started added additional projects with the same names to the sheet. The first two projects worked, but the third and fourth (with same name) do not.

    Is it possible to write a formula that says: look for Travel Week Start Date "Start Date" (Row 1298), then populate all children of Level 1 hierarchy (that contains Row 1298) with "Travel Week Start Date" value? Therefore, possibly allowing for duplicate project names?

  • Community Champion
    edited 02/04/25

    Hey @Ami Veltrie

    This should now work. Place this in row 1 and make this a column formula.

    =IFERROR(IF(AND(Hierarchy@row = 1, OR(CONTAINS("WASP", DESCENDANTS(Task@row)), CONTAINS("WASP", Task@row), CONTAINS("Compliance", DESCENDANTS(Task@row)), CONTAINS("Compliance", Task@row), CONTAINS("Programmatic", DESCENDANTS(Task@row)), CONTAINS("Programmatic", Task@row))), INDEX(DESCENDANTS([Start Date]@row), MATCH("Travel Week Start Date", DESCENDANTS(Task@row), 0)), PARENT()), PARENT())

    Does this work for you?
    Kelly

  • ✭✭✭✭✭✭

    Hi @Kelly Moore

    That is outstanding!🤩 Thank you so very much!!

    Yes, it works but thinking ahead for if/when we add additional Project Types…can the OR functions be replaced with a more generic parameter like "is not blank"? In other words, is the "Contains" call out detail necessary, or can it be replaced with a generic/scalable function?

    Do you foresee an issue with this formula:

    =IFERROR(IF(AND(Hierarchy@row = 1, OR(COUNT(DESCENDANTS(Task@row)) > 0, NOT(ISBLANK(Task@row)))), INDEX(DESCENDANTS([Start Date]@row), MATCH("Travel Week Start Date", DESCENDANTS(Task@row), 0)), PARENT()), PARENT())

  • Community Champion
    Answer ✓

    @Ami Veltrie

    If your formula above works, go for it!

    Here's an alternative:

    =IFERROR(IF(AND(Hierarchy@row = 1, CONTAINS("Travel Week Start Date", DESCENDANTS(Task@row))), INDEX(DESCENDANTS([Start Date]@row), MATCH("Travel Week Start Date", DESCENDANTS(Task@row), 0)), PARENT()), PARENT())

    Shout out if you need any help with anything.

    Kelly

  • ✭✭✭✭✭✭
    edited 02/05/25

    @Kelly Moore Hi Kelly! Yes, I like your alternative better. Thank you again!

    I do have another very similar question…can you help me in this string, or should I start a new one?

    Here's the gist:

    Need formula in the Project Type column that results in the Level 1 ancestor value from the Task column, for all it's children. Currently, it's resulting in parent.

  • Community Champion

    hey @Ami Veltrie

    What is the formula that you used?

    Kelly

  • ✭✭✭✭✭✭

    Hi @Kelly Moore

    =IFERROR(IF(Hierarchy@row > 1, PARENT(Task@row), Task@row), "")

    Also tried but it seemed to get me further from what I wanted.

    =IFERROR(IF(Hierarchy@row > 1, INDEX(ANCESTORS(Task@row), 1), Task@row), "")

  • Community Champion
    Answer ✓

    @Ami Veltrie

    Try this.

    =IF(Hierarchy@row > 1, INDEX(ANCESTORS(Task@row), 2))

    Does this work for you?
    Kelly

  • ✭✭✭✭✭✭

    @Kelly Moore Hi Kelly. Yes, that is working perfectly!

    Thank you so very much for all your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions