Help with "Syntax not quite right"

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

Try this.

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

Does this work for you?
Kelly

All comments

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?

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:

Hey @Ami Veltrie

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

Does that work for you?
Kelly

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?

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())

If your formula above works, go for it!

Here's an alternative:

Shout out if you need any help with anything.

Kelly

@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.

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), "")

Try this.

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

Does this work for you?
Kelly