Hi everyone,
I need to know how to copy a sheet in another file. I’m able with the Move and copy; but the problem it keeps the reference to the document where I took it. How to copy without the link? Because the formulas keep the link of the other file.
There should have a checkbox in the dialogbox to remove the link.
Thanks everyone
In the original sheet, press Ctrl-a to select all the cells, press Ctrl-c to copy. Open the other document, add a blank sheet and press Ctrl-v to copy. Then rename the sheet with the name of the initial sheet.
The formula come with the reference:
=IF(ISERROR(VLOOKUP(B11,‘file:///C:/Users/Phil/Documents/Untel/M-A Grille-1-xls-standardTest.xlsx’#$Liste.$A$1:$C$22,2,0)),"",(VLOOKUP(B11,‘file:///C:/Users/Phil/Documents/Untel/M-A Grille-1-xls-standardTest.xlsx’#$Liste.$A3:$C$22,2,0)))
In this case, once the sheet is copied, use the Find and Replace dialog and put the string
‘file:///C:/Users/Phil/Documents/Untel/M-A Grille-1-xls-standardTest.xlsx’#
into the find field and replace it with nothing. Make sure that you search inside formulas.
Sorry Steph1,
I know, but I looked to do with “move and copy” and nothing to do after; like:(There should have a checkbox in the dialogbox to remove the link). But your idea is good.
Thanks anyway.
Sorry to disagree. If the sheet already has some hardcoded file names in formulas, I don’t see why there should be a possibility to remove them from formulas. The result would be that some formulas would return an error!
This is not the issue! the file links are created and appear also when there are no “hardcoded file names in formulas”. As soon as there is an absolute reference to a cell in another sheet (in the same file), or with an absolute reference to another sheet in the same file. If you remove the absolute references the links are not created when you copy.
It seems there is no solution, I marked solved to avoid others answers. Thanks