Make formulas foolproof from deleted columns

Ramsay Zaki
Ramsay Zaki ✭✭✭✭✭✭
edited 07/22/22 in Smartsheet Basics

I am trying to make a formula that doesn't break whenever someone deletes a column. Example: We have 5 columns (Group 1, Group 2, Group 3, Group 4, Group 5). I then have a formula in a 6th column which sums the values in the first 5 columns together. But, some projects only have 3 or 4 groups... so the Project Manager wants to delete the extra columns in their project sheet. The problem is, when you do this, the Summation formula breaks and gets a #REF in it which causes the cell to be #Unparseable. I tried using IFERROR and ISERROR but I cannot seem to make this work without having the PM actually edit the formula. Any ideas?

· Share on FacebookShare on Twitter

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    How about just making them Editors instead of Admins? Then they won't be able to delete columns!

    You could also put a locked blank hidden column at both ends (call them Group 0 and Group 5ish LOL) and make your SUM formula:

    =SUM([Group 0]@row:[Group 5ish]@row)

    This says "add up all the columns starting with this one and ending with this one." That way, whatever columns they delete from in between those two columns won't affect the formula at all.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Answers

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video