Updating one formula reference is updating all formula references.

I have several formulas that reference a different sheet. When I update one formula it updates all other formulas that reference that sheet regardless that I've pointed them to different sets of data on that sheet:

For example - the above formula references the sheet Lafayette Square but the column with dollar values that I want it to sum.

This one references the same sheet but a different range on the sheet where I want it to count if the status is complete.

When I try to correct the reference to the correct range it then updates all the formulas that reference that sheet.

I've checked, none of the cells have the Column Formula on them so I don't think that's the issue.

I'm at a total loss...I've never had this happen before so I'm really not sure how to make it stop.

Find more posts tagged with

Accepted answers

SJ Sellers

From your screenshots, it looks like you are referencing the same range - "Account Profile - Lafayette Square Range 2" - in both formulas.

You need to create a NEW reference, with a different reference name, for the Status Column.

=COUNTIF( {Account Profile - Lafayette Square STATUS}, "Complete")

To do this, edit your COUNTIF formula, and completely delete "Account Profile - Lafayette Square Range 2". Then select Reference Another Sheet, and define a new reference being sure to give it a new name and highlight the proper column.

As a best practice, you should give your reference names more meaningful names to help in your troubleshooting.

e.g. "Dollar Values" and "Status" instead of "Range 2"

All comments

SJ Sellers

From your screenshots, it looks like you are referencing the same range - "Account Profile - Lafayette Square Range 2" - in both formulas.

You need to create a NEW reference, with a different reference name, for the Status Column.

=COUNTIF( {Account Profile - Lafayette Square STATUS}, "Complete")

As a best practice, you should give your reference names more meaningful names to help in your troubleshooting.

e.g. "Dollar Values" and "Status" instead of "Range 2"

Christina Reid

It would not allow me to rename the reference but after completely deleting (which I did before) the reference and retyping the formula, it worked.

JenniferN

This happens to me and it is very frustrating

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.