Omitting a workbook reference from a copied formula into another workbook

PeterWhitts
PeterWhitts used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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

Author

Commented:
I am using 2007 so where do I find that?
Rob HensonFinance Analyst

Commented:
Not sure off memory. Give me 5 and I will boot up the home pc. That has office 2007
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Rob HensonFinance Analyst

Commented:
Data ribbon - Connections pane - Edit Links.

Author

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?

Author

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.
Finance Analyst
Commented:
The edit links will do all links in one go!

Author

Commented:
Many thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial