Cross-sheet formulas

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

Find more posts tagged with

Support

Product Development

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Automatic Unalignment of Dashboard Widgets
Is anyone experiencing their dashboard widgets automatically being unaligned or they have moved a bit from your last save? I am the type where I like things clean and aligned. Some may say it's OCD. In Dashboard, this can mean the edges of my widget line up, each section is centered or same purposed widgets are the same…
How to restrict Form responses to data existing in the related Sheet
I am trying to create a Form that requires the user to supply information related to any item existing in the Primary Column. If I pull that existing field into my form, the user entry is a free-form field and I can't figure out how to restrict it (other than validating by data types). If I add a new field with a dropdown…
Sheet -Ganhtt Chart
Is it possible to display both the planned and actual duration for a single task on a Smartsheet Gantt chart? I would like the Gantt bar to show the planned duration (e.g., in blue) based on a Planned End Date column, and then show the actual duration (e.g., in green) based on an Actual End Date column. Can the Gantt view…