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
LVL 1
PeterWhittsAsked:
Who is Participating?
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
The edit links will do all links in one go!
0
 
Rob HensonFinance AnalystCommented:
Use the Links window to change the source of lookup data.

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
0
 
PeterWhittsAuthor Commented:
I am using 2007 so where do I find that?
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rob HensonFinance AnalystCommented:
Not sure off memory. Give me 5 and I will boot up the home pc. That has office 2007
0
 
Rob HensonFinance AnalystCommented:
Data ribbon - Connections pane - Edit Links.
0
 
PeterWhittsAuthor Commented:
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?
0
 
PeterWhittsAuthor Commented:
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.
0
 
PeterWhittsAuthor Commented:
Many thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.