Persisting Range Name Addresses to Avoid #REF!

[drewg]

There must be a technique of which i am ignorant to preserve the addresses or the formulas a range name refers to. A workbook has been returned from coworkers with unexplained #REF # errors in some range name addresses.

So, how is this best fixed, that is, where and how should this information be preserved so it can be refreshed?
Should i make a list of the names and their resolution (RangeA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) RangeB=Sheet1!$A$1:$A$100 ) and store it someplace?
If so, where should that happen, in a module as constants, in a workbook event, a hidden worksheet some other place like a form's activate but on the workbook level so it would be non-destructive of existing calculations and 'self healing' for dependent calculations while being automatically refreshing?
Thanks very much for your help and advice

 

[Gerald Higgins]

I suspect these #REF errors MAY have been caused by people deleting elements such as sheets, rows, or columns.

If that is the case, perhaps the best solution is to use some form of worksheet or workbook protection to prevent such changes.

Also, consider simply saving your own copy of the workbook, for use in such situations.

 

[drewg]

I didn't think of that, thanks very much, i appreciate it.