Replace Link - keep part of formula

[jlr123]

Hello,

I want to copy a worksheet from a workbook into another workbook but keep part of the formula. Delete the .xlsx file but keep the Bill Jones K15
portion
='[Total Commissions_Revenue Feb 2023 (Executive Only).xlsx]Bill Jones'!H15

Thanks for your assistance.

 

[combo]

You can try Find&Replace:
Find: [Total Commissions_Revenue Feb 2023 (Executive Only).xlsx]
Replace by: "" (leave empty)
Search in formula, in required scope.


combo

 

[Cole Mendoza]

Hello!

To copy a worksheet from one workbook to another while keeping part of the formula, you can follow these steps:

Open both workbooks in Excel.
Select the worksheet you want to copy from the source workbook.
Right-click on the worksheet tab and select "Move or Copy".
In the "Move or Copy" dialog box, select the destination workbook from the "To book" dropdown list.
Check the "Create a copy" checkbox and click OK.
In the new workbook, navigate to the cell with the formula you want to modify.
Edit the formula by replacing the file name with the new name of the workbook you want to keep. For example, if the old formula was:
='[Total Commissions_Revenue Feb 2023 (Executive Only).xlsx]Bill Jones'!H15
and the new workbook is called "New Workbook.xlsx", the modified formula would be:
='[New Workbook.xlsx]Bill Jones'!H15
Once you have modified the formula, you can safely delete the old .xlsx file while keeping the portion of the formula you wanted to keep. I hope this helps!

 

[jlr123]

Yes I understand, but I want all rows in my table to change to the new name of the workbook but leave the rest of the formula. i.e. '[New Workbook.xlsx]Bill Jones'!H15 next row would be '[New Workbook.xlsx]Bob Smith'!H15 etc for each row.
Thanks for your assistance.

 

[combo]

Why Search&Replace cannot complete the task?

combo

 

[jlr123]

When I tried search and replace, I got this pop up message:
"Excel found a problem with one or more formula references in the worksheet. Check that the cell references, range names, defined names, and links to other workbooks in your formulas are all correct.