Help with "Syntax not quite right"
![Ami Veltrie](https://us.v-cdn.net/6031209/uploads/userpics/8P0VG5OTWNRU/nHI3K0J42GX6A.jpg)
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
-
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
Answers
-
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:
=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
-
@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.
-
-
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 -
@Kelly Moore Hi Kelly. Yes, that is working perfectly!
Thank you so very much for all your help!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!