Another copy problem in Excel:
How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[WorkbookA.xlsx]!B!23 when copied over.
I want to maintain "relative" cell references instead of "absolute" cell references (i shall invent this terminology in Excel world if it does not yet exists).
Another possible alternative that I cannot get it to work is the option to paste cell "values" only. Excel treats "values" as calculated values rather than the actual formulas in the cell. If I choose paste formula, it still gives absolute references.
More About Why I Need This: I have a production xlsx in use for daily operations. We constantly need to make "upgrades" to this xlsx and so one person may create a copy and his changes there for a single sheet. Concurrently, another person may also be making changes to another sheet. Given that these sheets have no dependant cells on other sheets, like a summary report, it is desirable for us to just copy and merge the sheets back into the original xlsx. But the "absolute" referencing is giving a lot of trouble.
Answer
Try using Ctrl + ~ to display the formulas. Then use Ctrl + A to select everything, copy it and then paste it into notepad.
Finally, copy it out of notepad and paste it into your other workbook.
No comments:
Post a Comment