Index/Collect with Specific Criteria
I am trying to automatically populate the Parent "Content Development Status" column based on the most recent "Child" in the "Tasks" column, but there are certain Children rows in the Task column that I want to ignore. For example, if there is a webinar delivered, I do not want this to populate in the Content Development Status Parent Column, instead I want the most recent Child that is not webinar delivered to be populated.
I created a helper checkbox column that checks a box for all Children rows in the "Task" column that do not equal "Webinar delivered." I have a rather long if/then formula for all of the potential drop down options in the Task column, and it works for those pieces of content that do not have webinar delivery, but when I try it on content that needs the webinar delivered filtered out, it returns "Invalid Value."
Here is one part of the nested If formula that I am currently using:
=IF(INDEX(COLLECT(CHILDREN(Tasks@row), CHILDREN([Not Webinar/Workshop]@row), @cell = 1, CHILDREN(Start@row), MAX(CHILDREN(Start@row))), 1) = "Approved by Compliance", "Approved by Compliance")
Appreciate any insight that you have!
Best Answer
-
That worked - thank you so much!!
Answers
-
See how this works out for you.
=Index(Collect(Children([Content Development Status]898),MAX(Collect(Children(Start898),Children([Not Webinar/Workshop]898,1)))),1)
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
This came back as Unparseable
-
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Alright. Instead of Using INDEX COLLECT. Try an INDEX MATCH.
=INDEX(CHILDREN(Tasks@row), MATCH(MAX(COLLECT(CHILDREN(Start@row), CHILDREN([Not Webinar/Workshop]@row), 1)), CHILDREN(Start@row)))
Below shows the test I created.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
That worked - thank you so much!!
-
Well, actually it's reading the most recent entry, including "webinar delivered," which I want to ignore, if possible.
-
Do you have Webinar Delivered Checked?
my test shows only webinar delivered. When you have the not webinar/workshop checked on a webinar
I assumed you have an if statement in place to auto check. here I removed the auto check. Then checked the box that has Webinar delivered.
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
-
Nope
-
I am unable to replicate the issue. If you like add me on Teams and I can walk you through it.
Mark.Poole@whirlwindsteel.com
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!