Finding external links in a workbook

I have a workbook consisting of several largish worksheets emailed to me by 
a business contact. When I open it there is a warning that the workbook 
contains links to cells in other files (which of course I do not have 
because they are on my contact's computer not mine). Obviously I have to 
select the 'do not update' option.

How do I locate the cells where these links are incorporated? The document 
is too large to examine cell by cell.

BTW this is Excel 2000.

John 


0
11/8/2004 9:22:43 AM
excel 39879 articles. 2 followers. Follow

6 Replies
418 Views

Similar Articles

[PageSpeed] 59

Check under 'Insert --> Name --> Define whether there are ranges linked
to antoher workbook. If so, delete them or rename them.
Sometimes there are buttons or other objects linked to macros. You can
find these by selecting 'Edit --> GoTo --> Special --> Objects.
And my last option would be to check all charts. On the chart, click
right and choose Source Data. 
If you still get the message, I would make a copy of the file and
delete the worksheets one by one. Save and open and onze the message
doesn't show, you have determined the sheet with the link.


-- 
Roadie
------------------------------------------------------------------------
Roadie's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5936
View this thread: http://www.excelforum.com/showthread.php?threadid=276166

0
11/8/2004 9:53:50 AM
1. One way is to search for "[" which must appear in the formula of 
linked sheet.
2. If you do not mind losing all formulas, convert everything to value
:-
*select all sheet tabs with Ctrl + mouseclick
*click the grey box top left on one sheet where row & column header
meet to select the whole sheet(s)
* Edit/Copy
*Edit/Paste special/Values

--
Brian

-----------------------------------------------------------------------
BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5
View this thread: http://www.excelforum.com/showthread.php?threadid=27616

0
11/8/2004 12:07:23 PM
"Roadie" <Roadie.1fecty@excelforum-nospam.com> wrote in message 
news:Roadie.1fecty@excelforum-nospam.com...
>
> Check under 'Insert --> Name --> Define whether there are ranges linked
> to antoher workbook. If so, delete them or rename them.
> Sometimes there are buttons or other objects linked to macros. You can
> find these by selecting 'Edit --> GoTo --> Special --> Objects.
> And my last option would be to check all charts. On the chart, click
> right and choose Source Data.
> If you still get the message, I would make a copy of the file and
> delete the worksheets one by one. Save and open and onze the message
> doesn't show, you have determined the sheet with the link.

Thanks for the tips. However I do not necessarily want to delete or rename 
anything - I just want to find which cell each link is contained in. And 
deleting sheets one by one is no use to me - every sheet seems to have 
several of these links and I still won't know what cells they are located 
in.

John 


0
11/8/2004 1:01:51 PM
"BrianB" <BrianB.1feiuo@excelforum-nospam.com> wrote in message 
news:BrianB.1feiuo@excelforum-nospam.com...
>
> 1. One way is to search for "[" which must appear in the formula of a
> linked sheet.
> 2. If you do not mind losing all formulas, convert everything to values
> :-
> *select all sheet tabs with Ctrl + mouseclick
> *click the grey box top left on one sheet where row & column headers
> meet to select the whole sheet(s)
> * Edit/Copy
> *Edit/Paste special/Values.
>

Thanks Brian - that worked for me.

John 


0
11/8/2004 1:09:50 PM
use this
Try FINDLINK.XLA from http://www.BMSLtd.ie/mvp


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"John Rampling" <j.rampling@NOSPAM.com> wrote in message
news:DVGjd.13$u46.6@newsfe3-win.ntli.net...
> I have a workbook consisting of several largish worksheets emailed to me
by
> a business contact. When I open it there is a warning that the workbook
> contains links to cells in other files (which of course I do not have
> because they are on my contact's computer not mine). Obviously I have to
> select the 'do not update' option.
>
> How do I locate the cells where these links are incorporated? The document
> is too large to examine cell by cell.
>
> BTW this is Excel 2000.
>
> John
>
>


0
Don
11/8/2004 2:25:45 PM
Someday, that URL will change to:
http://www.oaltd.co.uk/MVP/Default.htm 

Don Guillett wrote:
> 
> use this
> Try FINDLINK.XLA from http://www.BMSLtd.ie/mvp
> 
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "John Rampling" <j.rampling@NOSPAM.com> wrote in message
> news:DVGjd.13$u46.6@newsfe3-win.ntli.net...
> > I have a workbook consisting of several largish worksheets emailed to me
> by
> > a business contact. When I open it there is a warning that the workbook
> > contains links to cells in other files (which of course I do not have
> > because they are on my contact's computer not mine). Obviously I have to
> > select the 'do not update' option.
> >
> > How do I locate the cells where these links are incorporated? The document
> > is too large to examine cell by cell.
> >
> > BTW this is Excel 2000.
> >
> > John
> >
> >

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/8/2004 10:11:26 PM
Reply:

Similar Artilces:

Finding a Median
I'm trying to write a query that will return a median for various values taken from a previous query. I've seen some suggestions in my searching, but I haven't been able to get them working. They are also all from before 2003 and refer to Access 97 and 2000. Has any functionality been added to 2003 for this? Or is there a non- code-based way to do it? I've seen it suggested to write a code to open the query, sort it, find the total number of records, divide it in half, then seek out the middle record using that value. I'm still very green when it comes to code, though...

Find a Value the first Time It Occurs
I have a row of values that shows the total cumulative number of sales of items by month. Occasionally, there may be no sales in a month for an item so the cumulative value would stay the same for more than one month. I want to select a number in the row the first time it occurs and not select it if it repeats. What are you wanting to do with the info? To return position (column number) of number 1234 within row 2: =MATCH(1234,2:2,0) A formula that signals it's the first occurence: =COUNTIF($A2:A2,A2)=1 This could be used in a helper row, or as a conditional format f...

Can't See External To: Names
Exchange 2003 SP1 Enterprise, Outlook Web Access 2003 From time to time a user of mine sends emails to external users plus himself. The problem is only his name shows in the TO: list. The external user names do not display. In the latest case, he was replying to an email received from an external sender. Everything looks fine in the tracking log for his sent email, all recipient names are listed. Needless to say he is not happy with this situation. Has anyone had this problem who can help me understand what's going on? Why are the external addresses unavailable for display?...

I need to compare 3 numbers and find the one in the middle
I have three numbers in a single row and would like to identify the middle number enter that number in another cell. Example: 1st # 2nd # 3rd # result 628 678 720 678 655 625 700 655 748 720 725 725 is there a function in excel that can do this? Thanks, Eddie Try =LARGE(A1:C1,2) VBA Noo -- VBA Noo ----------------------------------------------------------------------- VBA Noob's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3383 View this thread: http://www.excelforum.com/showthread.php?threadid=56811 fasteddie wrote.....

Find Duplicate names and delate
Dear experts, I have a small doubt could you clarify that??? That is I find duplicate name but I want to delete one name only, if I filter DUPLICATE….. both names are showing… 1. Select the range of data including the header. You need to have headers for these columns 2. From menu Data>Filter>Advanced Filter>Copy to another location 3. In 'copy to' specify the target cell and check 'Unique records only' 4. Click OK will give you the unique list -- Jacob "Find Duplicate names and delate" wrote: > Dear experts, > I have a small ...

Ressource Name for External Link
Dear All, When connecting a user and going to his project on PWA, Ressources names appear. However, if the link is an external to another project, the name of the External Ressource name does not appear. Does anyone know how to solve this problem? Thanks yann -- This is not a bug in the software. External links only include a small amount of information, and does not include the resources assigned to the external task. Furthermore, there is no way to force the external task to display resource names, but perhaps you could write custom code in VBA to extract this informa...

Not recieveing e-mails from external distribution lists
We run Exchange 2000. A client has two of our employees on a distribution list. Our client sends an e-mail to the distribution list, however our people do not always recieve the e-mails. The distribution list has been checked, and the e-mail address's are correct. I have double checked the server and there are no restrictions on who the people can recieve e-mails from. Anyone have any idea whats happening? ...

Find/Replace in RichEdit 2.0
I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my CRichEditDoc/View application so that I can use the ITextDocument interface and can do things such as suspend/resume the Redo buffer. Problem is, now the Find/Replace dialogs don't seem to do anything. If I revert back to RichEdit 1.0 they do! What's going on? BUMP! adriangibbons@yahoo.co.uk (Adrian Gibbons) wrote in message news:<34a2acd5.0404130713.123dbaab@posting.google.com>... > I'm using Windows ME and I've switched from RichEdit 1.0 to 2.0 for my > CRichEditDoc/View application...

Find (but not find)
My program takes a name from sheet3 goes to sheet1 to Find the name. If it cannot find name, how do you do an If/End to Exit Do while or find out if name has been founf? I have "On Error Resume Next" in program. Thanks again for all your help Gordon As ALWAYS, post your code for comments & suggestions. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Gordon" <gwelch1938@yahoo.com> wrote in message news:1184612089.486737.144020@n60g2000hse.googlegroups.com... > My program takes a name from sheet3 goes to sheet1 to Find the na...

Linking Contacts in Outlook 2007
I just converted from Outlook 2003 to Outlook 2007. In Outlook 2003 at the bottom left of the address card was a field in which you could link the open contact to other contacts in your address book. That field does not appear in the address card in Outlook 2007. Can someone help me figure out how to make that field appear? Thank you. That option is here: Tools > Options > Preferences > Contact Options... > Show Contact Linking on All Forms -- Russ Valentine "JMD01519" <JMD01519@discussions.microsoft.com> wrote in message news:BACF9C2B-6FAC-48F4-B...

Excel 2003
I am hoping to automate the extraction of information from a series of workbooks submitted by staff each week into a main spreadsheet. The importing information is laid out exactly in the same columns as the main spreadsheet but I do not know how many rows of information they may have input. What I would like to do is open the main spreadsheet, press a macro/ button that opens every file in a sub directory finds out how many rows of information there and then copy that info to the main spreadsheet, close the worksheet and then open the next one in the sub directory and repeat the ...

Find value in a column and insert rows above
The set up looks like this: ColU ColV ColW ColX Y N N N Y N N N N Y N N N N Y N N N Y N N N Y N N N Y N N N Y Y Columns will always be U through X and will always be sorted in this order. I need to find the first Y in each column and insert 2 rows above that row. On the blank row above the first Y, I need to highlight in yellow and put title in the first cell, such as New, Old, Existing, Deleted. Any help would be greatly appreciated. Thanks for your time, Dee If desired, send your file to my address below. I will only look if: 1. You send a copy of this ...

link .pub files
I have produced a series of .pub files [Publisher 2003] for a book - each has illustrations, footnotes etc as separate text or picture boxes. I now want to put all the chapters into a new .pub file so that I can take the complete book to a printer - but I can't find a way to do this! Best 'fix' so far has been to save each chapter as a Word file and import that into a new .pub file. But that does not deal with the pictures, which all have to be re-inserted in the precisely correct position. there MUST be a better way! Or is it another problem with Publisher, like the inabi...

Finding an event
Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button, a new window (in same workbook) opens with a new sheet. My problem is that excel does not seem to have any events for close of window if there are multiple windows in a workbook. Can someone help Peter Peter, That triggers the Workbook_WindowActivate event, you can use that. HTH Bob "Peter Ostermann" wrote in message news:i9m5v8$7bv$02$1@news.t-online.com... Hello, I am developing an app that uses a single worksheet to enter data. When user double clicks a button...

Linking tables together
I have set up five tables in my database but now I want to merge the tables into one table. How do I do that? If all the tables have identical structures, you can use a union query. Select * from tbl1 Union Select * from tbl2 Union Select * from tbl3 ..... "Maggie" <schmidt.margaret@gmail.com> wrote in message news:266ff9f6-f9b8-4f77-88ea-358518bfc936@q77g2000hsh.googlegroups.com... >I have set up five tables in my database but now I want to merge the > tables into one table. How do I do that? You are best not to. The information in the tables will refer to a sp...

Redirecting incoming mail to external email address
Hello! Our company started to hire freelance sales personel who are requesting a company email address instead of using their personal email address in order to look more professional. Is there a way without creating mailboxes to redirect any incoming mail to these addresses to their personal email? We do not want to have any mail destined for them to enter our system for security reasons. Thank you in advance! Lance You could setup a local domain account for the user, then set it to forward mail to an external contact with their personal email account information. We have several li...

Sum if Condition is Equal in Range Date and find column
I want to make a sum if Range is a week number and if style is Equal to CONC-92 or CONC-45 Week# 49 Week# 50 CONC-92= 27 CONC-92= 30 CONC-45= 27 CONC-45= 30 Datas are in a pivot table and... Pivot table looks like this: Date CONC-92 CONC-45 CONC-92 CONC-45 12/7 5 5 10 10 12/8 2 2 10 10 12/9 5 5 10 10 12/10 5 5 10 10 please help -- Lorenzo Díaz Cad Technician ...

How to make "This workbook contains links to other data sources" appear?
Hello How to make "Microsoft Excel This workbook contains links to other data sources To update all linked information, click Yes. To keep the existing information, click No." pop up or appear on the Task bar after clicking "Open Link" in "Linked Worksheet Object" in a context menu of a linked object in Word 2003 sp2? The window appear only after clicking an Excel button in the Task bar. ...

cannot find database
I have an excel spreadsheet that is supposed to update a access db. Whenever I try to save the .xls I get an error stating cannot find db. Even when I open the db with access, I get the error and the db opens anyway?????? This only happens on 2 out of 20 pc's and I cannot figure out why???????? Thanks ...

Linking contents of multiple cells to an external object
Hope I'm posting to the right forum. If not could someone please tell me where the appropriate place is. I've coded in Excel a register for a small college that allows the user to enter whether a student attended a particular subject. Each worksheet shows the whole class for a month, with students as rows & dates as columns & the subject appears in the cell. Totals appear on a seperate sheet. Where a student is absent there are a few alternative codes & on a seperate line room for a short comment. What is required is to automatically pull out the abscences on a separa...

Trapping a NO FIND after a find
I use the code below to store a row number to a variable after a find. I would like to trap a NO FIND if the find is unsuccessfull Any ideas. FSt1 provided the code below Sub macfindrow() dim rn as string dim rng as range dim therow as long rn = inputbox("enter something to find") if rn <> "" then Set rng = nothing Set rng = range("A1:IV65536").Find(what:=rn, _ After:=Range("A1"), _ Lookin:=xlformulas, _ Lookat:=xlpart, _ ...

Excel 2007: Formula bar text flicker, can't access other open workbooks or close Excel.
I am working on a fairly large application that refers to code in several addins. Userforms are used, but there are no shapes, charts, etc. on any worksheet. My problem is that 'randomly', but usually some time after using the main form (that can interact with cells on a worksheet) the test in the formula bar textbox starts flickering like some code is caught in an endless loop. I can interact with Excel at this point, but can't naviagate to other workbooks or even close Excel without ending it's process via TaskManager. The only way to get the flickering to stop is ...

office link
Can any one help. I have a error - null command file entry in swdrvs appear when i try to merge a letter in the card area of myob can any tell me how to fix this. Sounds like an MYOB problem to me. Look for assistance in the MYOB forums at http://community.myob.com/ -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professional basis. "gb007" <gb007@discussions.microsoft.com> wrote in message news:363BFB75-A646-473D-A813-00EB962374A7@microsoft.com... > Can any one help. ...

How do I find the out of office reply?
My out of office reply is missing from tools. How can I retrieve it? Out of Office reply <Out of Office reply@discussions.microsoft.com> wrote: > My out of office reply is missing from tools. How can I retrieve it? Unless you are using an Exchange server, you will not have the Out of Office Assistant. See this: http://www.slipstick.com/rules/autoreply.htm -- Brian Tillman ...

How to reduce the number of shortcuts/links in the recent documents list?
When I look into the folder: C:\Documents and Settings\<username>\recent then there are currently approx 20 recent documents listed. How can I permanently reduce the number or even disable this list? Cindy Cindy Parker wrote: > When I look into the folder: > > C:\Documents and Settings\<username>\recent > > then there are currently approx 20 recent documents listed. > > How can I permanently reduce the number or even disable this list? > > Cindy > Right click on the Start button and choose Properties --> Customize ...