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.
Auto-fill formulas and corrupt references
I'm currently in the process of setting up a rather formula-heavy project sheet, linking project names, project numbers and multiple assignees to all the different tasks within the project.
In order to circumvent the limitation of one assignee per task on my sheets, I've created additional (non-Contact list) columns with formulas referencing the cell above it to copy assigned names across multiple tasks. In order to do so, I got the formula to check if the current row is not empty, then check if it's a parent row and therefore the assignee shouldn't be copied from the row above, and finally copy the assignee in the previous row when this is not the case:
=IF(Type19 <> "", (IF(Stage19 = 1, Assignee19, AssignedParent18)))
=IF(Type20 <> "", (IF(Stage20 = 1, Assignee20, AssignedParent19)))
This formula works great as long as I don't remove any rows. In the above example, once I remove row 19, the formula of the updated row 19 looks like this:
=IF(Type19 <> "", (IF(Stage19 = 1, Assignee19, #REF)))
Is there a way to get this working properly, where SmartSheet auto updates the formula to refer to the cell above it?
Furthermore, while I was typing this, it occured to me that the auto-filling function in my sheet has stopped working. I followed the auto-filling instructions in your documentation, copying the fomulas across all rows to ensure it's both above and below the newly created row, but to no avail. The problem doesn't occur when I set up a new sheet, so I would really appreciate it if someone in your support team could have a look at the sheet for me.