Cross-sheet formulas

Jen_Arzt

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

 

Daniel Stein

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 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.

Jen_Arzt

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.

Yorg1001

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å

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