Reference to other sheets not changing in countifs when saving folders as new

NickB

Hi.  I have a smartsheet setup with multiple countifs to other sheets - all within the same workspace.  When I save the workspace as new, the references are not changing to the newly created sheets even though I have selected 'Newly created sheets' in the any reports, dashboards, sheet hyperlinks and cell links will refer to.

Without the ability to find/replace values in formulas, I have a real problem!

Any help would be appreciated.

Nick.

J. Craig Williams

Are you sure the reference has not changed? (the NAME will not) but it should be pointing to the new sheet correctly..

Craig

 

NickB

Hi Craig.  Thanks for your comment.  Yes I'm sure.  I've experienced this on a couple of occasions now and can easily recreate the issue.

The sheet that I have the issue with is being used to calculate values that are used in a dashboard.  The net result is that the dashboards don't work once the workspace has been copied - essentially because all the references are incorrectly pointing at the original sheets, not the new ones.

Despite there being a checkbox for newly created sheet, it doesn't appear to work.

Just to clarify, I'm saving as new a folder within one workspace into a different workspace.  The folder structure contains sheets which references other sheets in the same folder structure.  When I save new the folder structure, all the references within new sheets point to the original sheets not new ones.

If you save as new an entire workspace, the references are all ok - i.e. they point to the new sheets.  However this isn't a workable solution for my scenario.

So the issue seems to be that saving folders between workspaces doesn't correctly update the references to the newly created sheets.

J. Craig Williams

OK. Please change the title of your Post from "Workspace" to "Folders" as it is a different problem.

I will retest Folder>Folder Save as New today if I can. I haven't had my coffee yet, and can't think of where I might be using that in my workflows, but I thought I had checked it.

Craig

NickB

Thanks Craig.  I've done that.

I look forward to hearing back.

Hope you enjoy your coffee :-)

J. Craig Williams

I have not been able to reproduce a failure for the X-Sheet References to come over to the new sheets correctly.

I have tried Save As New to:

1. different workspace

2. same workspace

3. Sheets

All seem OK

Craig

NickB

Thanks for your help with this Craig.

I've spent many more hours trying to figure this out.  Essentially the fact that the name of the ranges don't change when you save as new a folder (and therefore sheets contained) into a new workspace as well as changing the name of the new sheets, makes this extremely confusing.  I've concluded that going forward the best practice is to use generic range names and not to relate them to the sheet (or workspace) name.  It seems that range names (certainly across sheets) don't have to be unique.

Not having the range names change automatically means that the first time you view the new sheets and dashboards, it appears that the references aren't correct.  Again, using generic names makes this less complex and means you don't have to rename all your references.

So essentially, I've done a lot of testing and am happy that smartsheet is working as advertised.

J. Craig Williams

I'm glad.

Yes, the names are not very helpful.

The problem with generic names is that they tell you NOTHING about the underlying data. That's like if we went into the library and all the books were labeled "Book". Thanks Mr or Ms Librarian, that does not help a lot.

If you are confused by {Project Sheet 1 | Date column} because it might point to the wrong sheet, wait til you have to check each x-sheet reference because the result looks wrong.

I hope that give us some sort of X-Sheet Reference Manager, but I don't expect them to, so I have parts of it already built in the API.

Craig

NickB

Hi Craig

I agree that the 'generic' name has to have meaning.  smartsheet current defaults the range name to include the sheet name.  We're prefixing sheet names with a customer name so when we're saving as new, we're unknowingly distributing customer names - even though they are 'hidden' in references.  The approach that we've landed on is to change the range names to reflect the column or data group, and to remove the customer name.

It's not ideal, and really smartsheet should be updating the range name automatically, but it provides a workaround at least.

J. Craig Williams

Apologies, the rant was not directed at your work-around, but rather the problems with the design by Smartsheet.

I use

{Sheet Name | Description}

as my naming convention, where

Sheet Name can be shortened or abbreviated.

and

Description is typically the column or parent/column, as brief as possible.

I keep a list of the references in use on a different sheet.

Craig