Copy VLookup formula from one spreadsheet to another

[tclark39]

I am by no means an excel guru, and I'm needing a little help. I have
numerous cells that are using the following Vlookup formula:
VLOOKUP("Total Amount ("&LEFT($C4,4)&")",Report!$B$1:$E$1000,2,FALSE))

The "Report" sheet will be present in all subsequent workbooks that I
create. Whenever I copy and paste the formula to another workbook,
Excel places the name of the file in the formula. I.E.:

VLOOKUP("Total Amount ("&LEFT($C15,4)&")",'[Week 14.xls]Report'!$B$1:$E
$1000,2,FALSE)

How can I prevent it from doing this, or is there something that I can
put in the vlookup to tell it to always use the current workbook?

Thanks in advance.

 

[aloomba]

You should Copy the Formula to Notepad and then copy (from Notepad) to the
second sheet to prevent it referencing the sheet it originally came from.

HTH

 

[Peo Sjoblom]

Copy the formula from the formula bar or if there are many, replace the
equals sign with something unique like


find what =

replace with ^=^

then copy over the text strings to the new workbook, then reverse the
replace in both workbooks

 

[tclark39]

Thanks for the suggestions.

I have found that if you go into Edit....Links....Change Source to the
current file, that this updates the formulas. I'd really like a way in
the function to tell it to always use the current workbook, but I
don't know if there is a way to do this or not.