Second reference is updating the first reference in another formula

I have a formula that looks up a Sheet to calculate Days Since Submitted. I have ANOTHER sheet where we move all Closed jobs once complete. I want to look that sheet up to calculate Days from Start to Completion. When I write that formula, the reference in my first formula changes. Why? How can I stop it? Thanks in advance

Best Answer

  • Itai Perez
    Itai Perez ✭✭✭✭✭✭
    Answer ✓

    Hey,

    I can understand the frustration, there is no problem with copying your formula but when you change the source highlight the source name and then instead of saying Edit Reference it will say Reference Another Sheet.

    this way it will only change the specific formula and not the reference across the sheet.


    Glad it worked for you!

    Itai Perez

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

Answers

  • Itai Perez
    Itai Perez ✭✭✭✭✭✭

    Hey,


    When you write the second formula, do you copy the first and click Edit Reference?

    Can you share some screenshots by any chance?

    Itai

    Itai Perez

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Hey, so here is formula 1

    =AVG(COLLECT({Days Since Created}, {Priority}, [Column2]$21))

    When I change {Days Since Created} to another range from another sheet, it edits the row I've already calculated, and a bunch of other Metrics I set up. I don't know why it changes all formula with that reference, and not just that cell....???

    Ok, so I was copying, and then just changing the reference; guess Smartsheet doesn't like that. I have just tried it and retyped and it worked :-)

  • Itai Perez
    Itai Perez ✭✭✭✭✭✭
    Answer ✓

    Hey,

    I can understand the frustration, there is no problem with copying your formula but when you change the source highlight the source name and then instead of saying Edit Reference it will say Reference Another Sheet.

    this way it will only change the specific formula and not the reference across the sheet.


    Glad it worked for you!

    Itai Perez

    If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂

    https://www.linkedin.com/in/itai-perez/

  • Ah! Ok! The subtleties of Smartsheet 😆

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!