Advanced formula copy question

[FoxyG]

Hello all, new to the forum but am deep into excel so I look forward to learning and helping! I have a question on formula copying into a new workbook. I have a workbook that is 41 sheets total, the last of which is an entire sheet of reference formulas pulling information from all of the other sheets in the workbook; this is a pricing workbook and each sheet is a different pricing record, pulling all prices together on the last page for further use. I would like to copy this last page to other workbooks to pull that data together in the same manner, but since the tab names are all unique to the product/workbook, all of the formulas point back to the workbook I copied it from. The formula reads:
='[workbook names.xls]worksheet name'!E$4]
As you can see it is a simple reference, but seems too specific to copy to a new workbook as is uses the workbook name and sheet name rather than more generic terms like Sheet 1, etc, which I can't change in the workbooks, they must stay specific. Thanks for your help!

 

[Derek Brown]

Hello and welcome to The Board.
I am not sure that I fully understand the problem ..... but when I want to copy a worksheet containing formulas to another workbook, in order to prevent Excel referencing the original workbook I first use find and replace to change the formulas to text.
For example, I would replace the "=" with something like "xyzabc" and then after copying the worksheet to another workbook, I then replace "zyxabc" with "=".
If you need to change worksheet names also, this could be done before converting the 'text' back to formulas.