formula changes when copied

Hi,
I copied a sheet from book1 to book2. The formulas in the sheet of book2 shows the name of book1 in the formulas. How can I remove these book1 references?

Example:
Formula after copying the sheet to Book2 is:
=IF('C:\Stony\[C5-C7_Corona.xlsm]Constants'!B14="","",'C:\Stony\[C5-C7_Corona.xlsm]Constants'!B14)

The formula in Book2 should look like this:
=IF(Constants!B14="","",Constants!B14)

Where Constants is a sheet in the the workbook.

Can this be done? I have 127 formulas in the sheet.

Khalil

If you select all the cells which you want to change then you can do it in one operation using Find & Replace (CTRL-H):

Find What: C:\Stony\[C5-C7_Corona.xlsm]

Replace with: (leave blank)

then click Replace All

Hope this helps.

Pete

Hi,
Thank you. This works fine.

The sheet I am copying has Validation Data with Range names. It gives a message that "you may not use references to other workbooks for data validation"

How can this be solved

Khalil

Go to Name Manager on the Formulas tab and adjust the Refers To: range as appropriate by deleting the path and filename.

Hope this helps.

Pete