Worksheet copy problem

J

Jac Tremblay

Hi everyone
When I copy a whole worksheet in another workbook, if the original worksheet has formulas (like lookups) that refer to some range another of its worksheets, the new workbook formulas use the original range in the original workbook. Is it possible to copy the sheet without the link or if not, to unlink the formulas so that they refer to the same range in the target workbook
I know that I can edit the formula by looking for the apostrophe characters (') and closed bracket (]) by removing some stuff in between, but I wander if there is not an easier way
The formula I get is
"=VLOOKUP(RC[-1],'[Original workbook.xls]Table'!R1C1:R6C2,2,FALSE)
when I want
"=VLOOKUP(RC[-1],Table!R1C1:R6C2,2,FALSE)
o
"=VLOOKUP(A1,Table!$A$1:$B$6,2,FALSE)
Thanks
 
D

Dave Peterson

Edit|links|change source and point at the new workbook itself.

or convert the formulas to text first
edit|replace
=
with
$$$$$

Then copy|paste

then convert them back to formulas (in both spots)

edit|replace
$$$$$
with
=



Jac said:
Hi everyone,
When I copy a whole worksheet in another workbook, if the original worksheet has formulas (like lookups) that refer to some range another of its worksheets, the new workbook formulas use the original range in the original workbook. Is it possible to copy the sheet without the link or if not, to unlink the formulas so that they refer to the same range in the target workbook?
I know that I can edit the formula by looking for the apostrophe characters (') and closed bracket (]) by removing some stuff in between, but I wander if there is not an easier way.
The formula I get is:
"=VLOOKUP(RC[-1],'[Original workbook.xls]Table'!R1C1:R6C2,2,FALSE)"
when I want:
"=VLOOKUP(RC[-1],Table!R1C1:R6C2,2,FALSE)"
or
"=VLOOKUP(A1,Table!$A$1:$B$6,2,FALSE)"
Thanks.
Click to expand...
 
K

keepitcool

try workking with named ranges?

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


Dave Peterson said:
Edit|links|change source and point at the new workbook itself.

or convert the formulas to text first
edit|replace
=
with
$$$$$

Then copy|paste

then convert them back to formulas (in both spots)

edit|replace
$$$$$
with
=



Jac said:
Hi everyone,
When I copy a whole worksheet in another workbook, if the original
worksheet has formulas (like lookups) that refer to some range
another of its worksheets, the new workbook formulas use the original
range in the original workbook. Is it possible to copy the sheet
without the link or if not, to unlink the formulas so that they refer
to the same range in the target workbook? I know that I can edit the
formula by looking for the apostrophe characters (') and closed
bracket (]) by removing some stuff in between, but I wander if there
is not an easier way. The formula I get is:
"=VLOOKUP(RC[-1],'[Original workbook.xls]Table'!R1C1:R6C2,2,FALSE)"
when I want:
"=VLOOKUP(RC[-1],Table!R1C1:R6C2,2,FALSE)"
or
"=VLOOKUP(A1,Table!$A$1:$B$6,2,FALSE)"
Thanks.
Click to expand...
Click to expand...
 
Top