# Apply Formulas to ONLY grandchild cells in the primary column

Options
✭✭✭✭

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,

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭
Options

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

How would that work?

• ✭✭✭✭✭✭
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!