Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Autofill - Formulas referencing above cell
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.