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.

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

" wrote:

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.

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

--
Regards,

Peo Sjoblom


wrote in message
ups.com...
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.

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.

On Saturday, 14 April 2007 at 00:42:08 UTC+8, wrote:
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.
Great thanks. Very helpful.