Auto-fill formulas and corrupt references

Beeksma

Hello,

 

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:

 

e.g.

Row 19

=IF(Type19 <> "", (IF(Stage19 = 1, Assignee19, AssignedParent18)))

 

Row 20

=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:

 

Row 19

=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.

 

Thanks,

Beeksma

Kennedy Stomps

Hi Beeksma-- Apologies for the delayed response on this! I can see that you emailed our Support team and it looks like my colleage Jeff was able to help you out. Glad to see he was able to help you with this issue!