Make formulas foolproof from deleted columns

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?

Find more posts tagged with

Accepted answers

Jeff Reisman

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.

All comments

Jeff Reisman

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.

Ramsay Zaki

I do want them to have the ability to delete the unnecessary columns... BUT, your blank hidden column was perfect. That worked. THANKS!!!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/337254#Comment_337254

I'm glad that worked for you! If you could mark my answer as accepted I would appreciate it. 😃

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.