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.

Best Answer

  • SJ Sellers
    SJ Sellers ✭✭✭✭
    Answer ✓

    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"

Answers

  • SJ Sellers
    SJ Sellers ✭✭✭✭
    Answer ✓

    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"

  • Christina Reid
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!