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

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Rob H
PeterWhittsAuthor Commented:
I am using 2007 so where do I find that?
Rob HensonFinance AnalystCommented:
Not sure off memory. Give me 5 and I will boot up the home pc. That has office 2007
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

Rob HensonFinance AnalystCommented:
Data ribbon - Connections pane - Edit Links.
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?
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.
Rob HensonFinance AnalystCommented:
The edit links will do all links in one go!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterWhittsAuthor Commented:
Many thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.