Named ranges in a formula not "actively" importing from cells expire.

Matthew@SIL

I folks. I think I found an edge-case in your new cross-sheet formula system.

I wrote some index formulae to pull related data from other sheets when the user chose some options from a list. All was working, and I was able to link, customize and test the named ranges.  I got my two sheets converted to the new system and was very happy as it all was working when I left the sheet to my colleagues.

Here is a simplified version of the formula:

=IF([Current target and progress]3 = "", "Competency Not Selected", INDEX({Source: English Growth Activities Range 1}, 1, VALUE([Current target and progress]3)))

As you can see, if CurrentTarget and Progress is blank, if does not do the cross-sheet lookup, but should do so once the value changes.

I read on https://community.smartsheet.com/discussion/cross-sheet-formulas that the named ranges are deleted after 2 hours if not "in use".

So, the problem is that if I set [Current target and progress]3 equal to "" and close and leave the spreadsheet alone (overnight), it forgets the range {Source: English Growth Activities Range 1}, and breaks the formula. When I come back, the "edit reference" option is no longer available because it forgot any range not actively in use. I have to re-create every cross-sheet reference.

The normal status of [Current target and progress] is empty so that the user can fill it when a new copy is created, so this will always break our template.

I have tested this by having similar ranges, one "actively" showing the content of a remote cell, and the other waiting to show content from a different range on user input. The second consistently unlinks after a time and breaks the spreadsheet if the index inside the if is not being fired.

Basically, if the above formula is true, the cross-sheet links expire after a few hours. If false, the links keep working.  

Please adjust your named range trash collection to account for any named ranges that are possibly mentioned in the formulae, and not only links that are "in use" at the current moment.

~Matthew Lee

 

J. Craig Williams

Contact support@smartsheet.com

...

I have x-sheet formulas that are not being directly called and they seem OK (I didn't check them all)

If any of the cells in the column are returning a value from the {Source: English Growth Activities Range 1} range, it should not be set to expire. 

Does the English Growth Activities sheet show <unknown sheet> or are the links just gone?

Craig