Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy problem
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 Tremblay wrote: 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. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy problem
try workking with named ranges?
keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool Dave Peterson wrote: 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 Tremblay wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet copy problem
Hi Dave
I tried both ways and they work fine Thanks a lot. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Copy Worksheet Problem | Excel Discussion (Misc queries) | |||
Worksheet copy problem - local names | Excel Discussion (Misc queries) | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming | |||
worksheet copy problem | Excel Programming | |||
worksheet copy problem | Excel Programming |