Apply Formulas to ONLY grandchild cells in the primary column

Ryan Holguin

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,

Mike TV

@Ryan Holguin

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.

Mike TV

@Ryan Holguin

Also for your sheet, you'll need to change the first formula to:

=IF(COUNT(ANCESTORS([Product Development]@row)) >= 2, 1, 0)

Mike TV

@Ryan Holguin

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.

Ryan Holguin

@Mike TV - Okay it looks like I only want to count the great-grandchildren (highlighted rows below):

How would that work?

Mike TV

@Ryan Holguin

Then you'd use either:

=IF(COUNT(ANCESTORS([Product Development]@row)) >= 3, 1, 0)

or:

=IF(COUNT(ANCESTORS([Product Development]@row)) = 3, 1, 0)