asked on Omitting a workbook reference from a copied formula into another workbook
I have copied a worksheet from one workbook to another where on the worksheet I have formulas that refer to another sheet common in both workbooks..eg
Book1 sheet(MyData) sheet(MyLookUp)
Book2 sheet(MyData) copied sheet(MyLookUp) from Book1
My problem is that the copied sheet formulas still refer to Book1 sheet(MyData)
I need the copied MyLookUp formulas to now refer to exactly the same structure in Book2 sheet(MyData) eg to local sheets in the local workbook.
How do I overcome this please
Book1 sheet(MyData) sheet(MyLookUp)
Book2 sheet(MyData) copied sheet(MyLookUp) from Book1
My problem is that the copied sheet formulas still refer to Book1 sheet(MyData)
I need the copied MyLookUp formulas to now refer to exactly the same structure in Book2 sheet(MyData) eg to local sheets in the local workbook.
How do I overcome this please
Microsoft Excel
Last Comment
PeterWhitts
ASKER
I am using 2007 so where do I find that?
Not sure off memory. Give me 5 and I will boot up the home pc. That has office 2007
Data ribbon - Connections pane - Edit Links.
ASKER
Well Thats Office button / Prepare / Edit Links but how do you edit four hundred formulas for links to that other workbook....there has to be a better way than that?
ASKER
Isn't there a simple way of copying/moving a sheet from one workbook to another workbook without the links updating (temporary fooling it) before reconnecting it to the new datasheet in the new workbook.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Many thanks!
Excel 2003 menu path - Edit > Links - select linked file and click change source. Browse to new file (book 2 in your case) and accept.
Thanks
Rob H