Multiple cells locked to single formula in same column

idenroc

I'm relatively new to Smartsheet and use it as a tertiary tool, so apologies if this is a 'basic' question.

What I want is to tweak a formula on one row of a column and have it apply all other rows that reference that cell in the same column so I don't have to copy the tweaked @row + Parent, Children, or Ancestors (sometimes) formula all the way up the column manually, potentially missing some cells.

Thanks in advance!

Paul Newcome

Column formulas wouldn't take away the ability to have parent row formulas separate from child row formulas. It would work similar to my previous example.

You have one formula you want on parent rows:

=SUM(CHILDREN())

You have another formula you want on the child rows:

=[Phase Fee]@row / PARENT([Hourly Rate]@row))

You combine them like so:

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

=IF(COUNT(CHILDREN()) = 0, [Phase Fee]@row / PARENT([Hourly Rate]@row)), SUM(CHILDREN()))

Dan W

Looks like you may want the "Column formula" option.

Here is a link to the How to article

https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

idenroc

@Dan W Thanks for your response!

That is close to what I'm looking for but not quite for 2 reasons. First, I don't have a "Business Plan or Higher", which is required to apply column formulas 🙄. Second, some cells in the column might be a parent to cells below, which will contain something like =sum(children()) to get a total for the values calculated below, so can't have one formula to rule the column.

Dan W

What about using the $ to lock in a cell?

Such as [Column]$3

idenroc

@Dan W that returns (duplicates) the computed value from "[Column]$3" rather than applying the formula with values from the new context.

Paul Newcome

You would need to use an IF statement that basically counts the children and applies the child row formula to the rows that have a count of zero and the parent row formula to the other rows.

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

If that doesn't quite get it for you, are you able to provide some screenshots for context/reference?

idenroc

Paul, thanks for the response and a great idea but I need the parents to be able to have an independent formula.

I'm trying to build a Gantt architectural project tracking log that tracks project totals (Parent row) and project phase variables (Children). I would like them all to reference and execute a base formula that can be tweaked, if necessary as columns are added or my formula writing gets better, and applied to all projects in the sheet instead of having to copy and paste across the whole column

Here's an example to calculate the number of hours to allocate to each phase that then gets totaled in the Parent row:

Parent cell: =SUM(CHILDREN())

Children (usually between 1 & 6 rows, varies per project): =[Phase Fee]@row / PARENT([Hourly Rate]@row))

[PhaseFee] is a dollar amount for that phase of the project and is generated by a formula based on an overall project fee; [Hourly Rate] is only on the Parent b/c it applies to all phases of the project. The goal is to calculate the total number of hours that are available for the phase based on the fee.

I've simplified the child formula to get the point across but it will typically include at least one IF function among others.

Ideally this would function similar to using "$" ([PhaseFee]$125) but execute the formula on row 125, not just report what is in that cell.

The more I think about it, the more I think that column formulas might be my only option. This takes away the Parent SUM(CHILDREN()) function, so forces more columns in a sheet I was trying to keep relatively simple, but it just might be what it is.

Paul Newcome

Column formulas wouldn't take away the ability to have parent row formulas separate from child row formulas. It would work similar to my previous example.

You have one formula you want on parent rows:

=SUM(CHILDREN())

You have another formula you want on the child rows:

=[Phase Fee]@row / PARENT([Hourly Rate]@row))

You combine them like so:

=IF(COUNT(CHILDREN()) = 0, child_row_formula, parent_row_formula)

=IF(COUNT(CHILDREN()) = 0, [Phase Fee]@row / PARENT([Hourly Rate]@row)), SUM(CHILDREN()))

idenroc

Yes, that makes total sense, though the Column Formulas will get pretty complex. Thanks, Paul!

Now I just need to figure out how to get Column Formula functionality. On the pricing page, it says all levels have access to that feature. When I try to use one, it tells me I need a Business license or higher. We also have a legacy "Team" license that has apparently been replaced with "Enterprise" and I'm guessing "Team" isn't really recognized anymore and is likely why I don't have that ability. Separate issue that I've raised with the help desk, though.