Cross-sheet formulas

edited 12/09/19 in Using Smartsheet
02/06/18 Edited 12/09/19

I love that we're now able to use ranges from other sheets in formulas.  I just updated a summary sheet so it uses formulas linked back to worksheets for each member of my team, rather than having the formulas on each team member's sheet and linking from there.

I have a couple of questions about the best way to use the new feature.

1) I named a set of cells on a sheet I was linking to, and then decided I wanted that name for a different set of cells.  I renamed the first set of cells, and then tried to use the name for the second set.  I got the message, "This name is already being used on this sheet.  Please enter a different name."

Is there a list of named cell ranges that I can access, so I can delete a range name if necessary?

2) In using this feature to create a summary sheet, I wanted to copy a formula so I could apply the same calculation to a worksheet for each member of my team.  I created a formula referencing cells from one team member's sheet, copied it into a new cell, and updated the cell ranges to link to a second team member's sheet. 

When I did this, Smartsheet changed the links in the formula for the first team member to link to the second team member's sheet (i.e. so both formulas link to the same "outside" sheet).  Can I prevent this from happening?

Thanks,

Jen

 

Comments

  • Hi Jen,

    Glad you like the new feature. To answer your questions:

    1) Currently, named ranges are automatically cleaned up after 2 hours, once they are no longer being used. We are looking into also adding a way to manually delete names in a future release.

    For what you were trying to do: instead of renaming the range, you could try editing it and selecting a different set of cells or columns directly.

    2) When you edit a named range, all instances of that range will be updated. If you want to create a distinct reference, then instead of clicking "Edit reference," you can delete the reference in the formula, then click "Reference Another Sheet" to insert a new reference.

    Best regards,

    Daniel

     

  • Jim HookJim Hook ✭✭✭✭✭

    I'm excited about this new capability and wishing it had been here years ago when I was first putting my app together using many thousands of links to pipe the same project data to other Smartsheets. I'm going to play around with it as time permits before jumping into it to make sure I understand how to best use it.

  • Thanks for the response!  Both options for my first question sound like they would solve the problem.  For my second question, I wasn't editing a named range - I was inserting a new named range into a formula I had copied into a new cell.  When I did that, my original formula was also updated, which I did not want.

  • I would like for a way of clearing the named range so I don't have to wait 2 hrs.

    I have 30+ projects using a vlookup to a sheet. All the links are broken because the range name number can't be reset. 

     

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi,

    Great idea!

    Please submit an Enhancement Request when you have a moment.

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.