Autofill - Formulas referencing above cell

B_SC

Hi,

 

I have one column creating a reference numbers for a hierarchy from three hidden columns similar to those using a format of 1.1.01.

My Level 2 and 3 formulas are as follows

=IF(COUNT(ANCESTORS()) = 0, <ABOVE CELL> + 1, <ABOVE CELL>)

Number increases with each row, except where it is 2 or more layers in, in which case it stays the same as above

 

=IF(COUNT(ANCESTORS()) > 1, <ABOVE CELL> + 1, 0)

Number increases only when 2 layers or more in, and otherwise goes to 0

 

My issue is when others are inserting new rows into the sheet, these formulas do not autofill like the others, and the formula in the cell below now refers to the cell 2 rows above it. Is there anyway to make these formulas autofill and have the one below not maintain the cell it was linking to, but the newly inserted row.

EssentiallyI'm after a way to have the formula look for its current cell with the row number -1 always.

As these columns are hidden and locked to others, I need a way that people can add new rows and have the reference system continue.

Thanks 

 

Shaine Greenwood

Hello,

There isn't a way to control the behavior of the auto-fill formulas feature. Formulas must be the exact same on 2 rows above or below on the same hierarchy for the feature to work. Since you're referencing rows above, it's likely that when people add new rows, they're changing the cell reference pattern to make the formulas different. 

Correct me if I'm wrong, but I think you're trying to create a WBS in your sheets. If that's the case, we don't have this feature in Smartsheet but our Product team is highly interested in getting feedback on how you'd like to see it implemented. If you have a moment, submit your feedback via the Product Enhancement Request form under Quick links on the right of the community site.