Can formulas be copied over to another sheet by the automation workflows?

Here's the scenario:

I have a scoring template that I intend to use for different site locations to gauge their several programs and their current state. This template has formulas imbedded to calculate scoring for each program. So let's say Sites A, B, & C all complete their template, and these sites are part of Division 1.

I want to rollup this information to assess Division 1's overall program efficacy. The idea is to then create an automation workflow to essentially copy and paste each site's data into one single sheet for another individual to review & make assessments based on the scoring. But since the sites have the same programs, they show up as multiples, which the reviewer would make their determination for an overall score for each program. This is a manual process in which the duplicate program rows are deleted and the remaining row is adjusted by the reviewer to establish the Division 1 score for each program.

The problem: rows that are copied to another sheet don't maintain the imbedded formulas, and therefore would have to be calculated manually when the scoring is changed by the reviewer on the Division 1 sheet where the data is copied to.

The question: Is there a way for the formulas to follow with the copied rows so when changes are made to the rollup sheet (where the workflow copies the rows to), the scoring calculates as well?

Find more posts tagged with

Accepted answers

All comments

Matt Lynn ACT

@KarenTF

I hope I'm understanding you're situation, so here's my attempted response.

Simple: Formulas do not copy. If you have a formula =2+2, the copied destination will receive 4, not the formula.

Presumably your source and destination sheet will have the same column and since you're copying a row with an automation, you'd have this formula be a column formula so all rows have this formula. The proper way to do this is to add to be sure both sheets have this same formula as a column formula. This way the other input columns which have values that feed the formula will be copied and the result of "4" will be calculated separately but all in all be the same result.

AnnaB

Why would they not copy the formula? If people wanted "4", they'd input "4".

But if you're wanting to calculate the value of cell X + cell Y (for instance) which may vary, copying a static result is not helpful.

This is one of the most infuriating limitations. I'm trying to create automated start/due dates based on the following:

=TODAY() - WEEKDAY(TODAY()) + 3

However, when the rows automatically copy across to my Work in Progress sheet at their scheduled intervals, I have a static date from when I entered this formula into my "template" sheet.

Really hope this changes.

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.