Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Tuesday, 04 January 2022
  1 Replies
  5K Visits
0
Votes
Undo
Is there a Kutools function to automatically strip out references in formulas to other workbooks? 
eg to change every reference that looks like this 
'[anotherfile.xlsx]Worksheet name'!$col$row

to this 
Worksheet name'!$col$row


I'm new to Kutools (love it) and can't find a way of doing this, other than via a pretty manual find-and-replace.
2 years ago
·
#2425
0
Votes
Undo
Hi robertvarnam1,

Sorry that we don't have such feature. However, with our Find and Replace in Multiple Workbooks feature, you can find-and-replace across multiple workbooks and worksheets. To do so, please follow the steps below:

1. Under Kutools tab, click Find Find and Replace in Multiple Workbooks.
2. Click the Replace tab, type [*] in the Find what box, and leave the Replace with box empty.
3. Select the workbooks and worksheets with references in formulas to other workbooks you want to remove.
     batch find and replace.png
4. Click Replace All, you will see all the workbook names in the formulas are removed.

Note that you don't have other values that are enclosed with [ ] that you don't want to remove. If you do, in the Find what box, you will have to add the actual workbook name, for example, [Book1].

For more details of the feature, please see this tutorial: https://www.extendoffice.com/product/kutools-for-excel/excel-find-replace-in-multiple-sheets-files.html#a3

Amanda
  • Page :
  • 1
There are no replies made for this post yet.