Is It Possible To Clear Entire Sheet Without Clearing Formulas

Hi everyone,

I have a smart form that takes input from a user. This data then goes into the sheet - where there are complex formulas within to make certain calculations - later used to create certain metrics etc.

The nature of this sheet means that it will be continually added to, meaning at some point, it will be a good idea to clear data out of it.

The issue is that clearing all the cells for a fresh start seemingly means that all the formulas are also cleared.

What is my best option here? My only thought is to have one permanent "Control" row which acts as a placeholder so that the formulas can keep replicating.

Any thoughts and suggestions welcome, thank you for your time.

Alex

Find more posts tagged with

Accepted answers

Kelly Moore

Hi Alex

I'm sure you'll receive many suggestions from the community.

I wondered if it was necessary to completely wipe your entire sheet all at once? Moving rows intermittently would mitigate the chance of all formulas being entirely deleted. The action of 'Move Rows' can be triggered either manually or through automation and transfers the row(s) from the current sheet to another sheet. This other sheet can be maintained as an archive (formulas aren't transferred, only cell values) or the moved rows can be deleted manually from the new sheet without worry.

Also, since you mentioned your complicated formulas, if you haven't already found the new formula functionality, the formula-columns might be useful to you.

cheers,

Kelly

All comments

Kelly Moore

Hi Alex

I'm sure you'll receive many suggestions from the community.

Also, since you mentioned your complicated formulas, if you haven't already found the new formula functionality, the formula-columns might be useful to you.

cheers,

Kelly

AlexP

Thank you,

I did not know I could change the entire column to be a formula column. This is helpful, as I can clear rows without risk of clearing the formula.

Much appreciated.

Alex

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.