Hi,
I m parsing another sheet and using references in my formulas, something like this :
=$mydatatoparse.B3
I want to update my data by substituting the sheet “mydatatoparse”. I m deleting the sheet and inserting a new sheet with the same name but all my formulas are modified with a #REF !. How can avoid this and getting my formulas untouched ?
Thanks,
jérémie
First create the new sheet with a related name like mydatatoparse_new, then adapt the formulae using ‘F&R’ with option to search in formula. Serach for mydatatoparse and replace with mydatatoparse_new. Now you may delete or rename the old sheet and rename the new sheet from mydatatoparse_new to mydatatoparse. The references will adapt autoamtically.
thank you, it’s a good idea… it wiil do the job but i m searching for a more practical and straight solution.
How to find something “practical” often depends of very specific details of the situation. Assume you only need references into “the other sheet” in places where the actual cell address can easily be calculated. You may use INDIRECT referencing then:
=INDIRECT("mydatatoparse." & ADRRESS(paramRow; paramColumn))
I would, however, suppose there are many cases where this approach is not quite “practical”.
I’m having the same problem, which of course can be solved with find & replace, in my case by only updating all #REF!:s with the name of the sheet. Or by copying the new data (deleting the new sheet) and pasting on top of, replacing the old data, which requires least steps. (I’m working on a SikuliX automation process, every key press counts, trying to avoid mouse actions.)
But shouldn’t there be a way to:
a) lock the absolute cell content, so that it would reference to a non-existing content and print an error rather than giving the error in the cell content?
b) import data into the current sheet rather than making a new one?
c) rename a sheet with a same name as another, prompting if replacing the other is what the user desires, effectively deleting one sheet?
a) No, because the reference is to a sheet, not its name; that’s how renaming a sheet displays the new sheet name in all formulas.
b) That’s already possible, simply overwrite (or delete then fill) cell content in an existing sheet; or use menu Sheet → Link to External Data… to refresh data from an external file; or use external references to another document; or use Sheet → Insert Sheet from File… once and enable Link, which can be updated later.
c) I doubt that would be a good idea.