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
    Itai ✭✭✭✭✭✭
    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

    Project Manager | Transformation Department

    Gong cha

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

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

Answers

  • Itai
    Itai ✭✭✭✭✭✭

    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

    Project Manager | Transformation Department

    Gong cha

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

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

  • 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
    Itai ✭✭✭✭✭✭
    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

    Project Manager | Transformation Department

    Gong cha

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

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

  • Ah! Ok! The subtleties of Smartsheet 😆

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!