Copy Excel worksheet and maintain relative cell reference in formulas?

Question

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 1

I can copy a data cell and a =formula that uses it both from worksheet A in workbook Q to another workbook without the new =formula referencing worksheet A in workbook Q .

When I use a =formula on worksheet A to refer to data on another worksheet B both in workbook Q, copying worksheet A into another workbook carries with it the reference to data in worksheet B because worksheet B is not being copied. You should either move your data into the worksheet that you are duplicating or duplicate the whole workbook.

You have something else going on:

'=B!23' is not a simple assignment; '=B23' is. '=B!23' references the worksheet 'B' as the exclamation is the workbook and worksheet separator. You should not be able to enter this construction. Neither I nor Excel knows what '23' as a formula by itself is. The reference should be in the form in the form '=[workbookName]worksheetName!cell' (not '=[workbookName]!worksheetName!Number).