Help with formula to check the lowest hierarchy row per branch where FX = 1

JThour

I'm trying to create a formula in Smartsheet that checks the box only for the lowest hierarchy row in each branch where the FX column shows "1".

• Column "A" represents the hierarchy level.
• Column "FX" contains the FX rate, and I only want to check rows where FX = 1.

🔹 The checkbox should be marked only for the lowest row in each branch where FX = 1.
🔹 If a parent has FX = 1, it should only be checked if no lower child in that branch also has FX = 1.
🔹 The formula should work independently for each branch of the hierarchy.

I've tried using ChatGPT, but the formulas provided either:
❌ Check all rows where FX = 1, or
❌ Only check the lowest child in the entire sheet rather than per branch.

I'm attaching a picture of what I want the result to look like.

Any and all help is greatly appreciated! Thank you! 🙏

Paul Newcome

You will need a helper column (called "Helper" in this example) that outputs the branch on every child row. If your branches are the parent row, you would use something along the lines of:

=PARENT([Column Name]@row)

Then you would need an auto-number column (called "Auto" in this example) and a text number column (called "Row" in this example) containing the following column formula:

=MATCH(Auto@row, Auto:Auto, 0)

Finally the formula to check the box would be…

=IF(COUNT(CHILDREN(Auto@row)) = 0, IF(Row@row = MAX(COLLECT(Row:Row, Helper:Helper, @cell = Helper@row, FX:FX, @cell = 1)), 1))