How do I copy a formula and update a cell reference

I'm an excel user, but new to Smartsheet so I'm still trying to figure out how it works. Please see screenshot attached for reference -

I have a formula in the "Yes_2" cell (circled in red). That formula references another sheet "CSF Questionnaire Range 7" (highlighted yellow).

Next, I copied the formula and pasted it in "No_2" (circled in green). Next I would like to update the reference range on the "CSF Questionnaire", however when I change the reference, it also changes the reference in the "Yes_2" cell, thereby changing the output.

Is there a way to fix this, or do I have to manually type a new formula in each cell that references a new range on the CSF Questionnaire?

Find more posts tagged with

Formulas

Accepted answers

Bassam Khalil

Hi @Lucas Okwudishu

Hope you are fine, you must change the name of the range reference for each cell the following is a sample for copying the formula

1- Villa Count = COUNTIF({MP1111_DRAWING_TRANSMITTAL-Villa}, "MV")

2- Floor Count =COUNTIF({MP1111_DRAWING_TRANSMITTAL-Floor1}, "BF")

please read the following article it will help you

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Paul Newcome

If you "Edit" a reference, it will update all occurrences in the sheet. If you enter the cell to edit the formula and highlight the entire cross sheet reference, the link in the helper box should change from "edit reference" to "reference another sheet". Follow the usual steps for creating a cross sheet reference, and this should update the formula with the new range without affecting the other formulas within the sheet.

All comments

Bassam Khalil

Hi @Lucas Okwudishu

Hope you are fine, you must change the name of the range reference for each cell the following is a sample for copying the formula

1- Villa Count = COUNTIF({MP1111_DRAWING_TRANSMITTAL-Villa}, "MV")

2- Floor Count =COUNTIF({MP1111_DRAWING_TRANSMITTAL-Floor1}, "BF")

please read the following article it will help you

https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Paul Newcome

Corporate Corporate

@Paul Newcome You are the BEST!

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.