Apply Formulas to ONLY grandchild cells in the primary column
All,
Is this possible? I am looking for a way to automate a process for our team. I have a few columns that will provide a product name, project type and then the tactic name. The tactic name will always be equal to the Product name + the Project Type. Here are some examples with the current formula I am using (I have to add the " " since smartsheets won't add a space:
=[Product/Campaign/Sustained Marketing Name]@row + " " + [Project Type]
Is there a way to have this formula automatically populated every time a grandchild row is created?
Let me know if you need more details or if that is not clear.
Thank you,
Answers
-
Try this. Create a helper column which you can hide after it's created. Here's an example:
Identify column formula (this is your helper you create):
=IF(COUNT(ANCESTORS([Primary Column]@row)) >= 2, 1, 0)
Then you can create a formula such as:
=IF(Identify@row=1, [Product/Campaign/Sustained Marketing Name]@row + " " + [Project Type], "")
Of course, if the column you want this formula in is also used for other things, you'll possibly need to add to this formula or adjust it to make it work.
Let me know if you run into any challenges with this.
-
Also for your sheet, you'll need to change the first formula to:
=IF(COUNT(ANCESTORS([Product Development]@row)) >= 2, 1, 0)
-
If you have any great-grandchildren rows which you do not want identified, you'd change the formula to this:
=IF(COUNT(ANCESTORS([Product Development]@row)) = 2, 1, 0)
Then this will only count the actual grandchildren rows and not their children rows.
-
@Mike TV - Okay it looks like I only want to count the great-grandchildren (highlighted rows below):
How would that work?
-
Then you'd use either:
=IF(COUNT(ANCESTORS([Product Development]@row)) >= 3, 1, 0)
or:
=IF(COUNT(ANCESTORS([Product Development]@row)) = 3, 1, 0)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!