Color Cells With External Links On Update

Feb 27, 2008

I have the following code in Sheet1 for file Book7.xls

Private Sub Worksheet_Calculate()

On Error Resume Next

For Each rcell In Range("A1:D6")
Select Case rcell.Value
Case Is >= 5
rcell.Interior.Color = vbBlue
Case Is < 5
rcell.Interior.Color = vbRed
End Select
Next rcell

End Sub

Range A1:D6 is linked to an external source file which I am changing every now and then.

A1 has the formula:


Copied all the way to D6.

[Book8.xls]Sheet1!A1:D6 presently has all values 1 hence the formatting in Book7 is all red.

Now I an changing source file to Book9.xls (thru EDIT menu->LINKS). [Book9.xls]Sheet1!A1:D6 has all values 10. But when I change external source to Book9.xls, the right formatting did not apply. All are still red (instead of blue). I still need to go in one of the cell and press F2 and F9 to trigger calculation.

How do I change the code in order to recognise the calculation in order to trigger the codes in the event?

View 9 Replies


Identify / Color Formula Cells With External Links

Feb 22, 2008

I have a sheet called "Sheet1" in workbook "File1". In this sheet, I have a lot of formulas and some of them involves linking to external files located in E: Finance folder. Is there a way to automatically highlight those cells containing a formula that references to external files?

View 3 Replies View Related

Update And Then Break External Links To Another Workbook

May 21, 2014

I am new in VBA, but I have a short procedure to break all external links in the currently active workbook.Is there any option to firstly update all external links and only then break them?

Sub BreakLinks()
Dim Links As Variant
Dim i As Integer
With ActiveWorkbook
Links = .LinkSources(xlExcelLinks)
If Not IsEmpty(Links) Then
For i = 1 To UBound(Links)


View 5 Replies View Related

Automatically Update External Links To Csv File In 2007

Jan 28, 2010

I have an Excel 2003 workbook (named TargetWorkbook.xls) with some links to a CSV file called DataSource.csv. I use Excel 2007 to open the workbook. I checked the "Update links to other documents" option under the "When Calculating this workbook..." section on the Advanced pane of the Excel Options window. I unchecked the "Ask to update automatic links" option under the "General" section on the Advanced pane of the Excel Options window. I selected the "Don't display the alert and update links" option on the Startup Prompt dialog (accessed from the Edit Links dialog) I created a simple Auto_Open macro with the following statements:

Sub Auto_Open()
Workbooks.Open Filename:="C:ProjectsExcelTestDataSource.csv", ReadOnly:=True
Windows("DataSource.csv").Visible = xlVeryHiidden
End Sub

The DataSource.csv file is updated daily. I want the cells of TargetWorkbook.xls to automatically update from the csv file when I open TargetWorkbooks.xls without displaying a prompt. I plan to deploy this workbook to a server and open it programatically via a Windows Service (I know, I know, Microsoft doesn't encourage this)and can't have it throwing up a user dialog.

Here is the problem: When I open TargetWorkbook.xls manually using Excel 2007, it updates the links but throws up the dialog "This workbook contains one or more links that cannot be updated...". When I click on the "Edit Links..." button, it displays the message "Warning: Open source to update values". When I click the "Check status" button for this link, it says "Source is open". How do I prevent this dialog box from poping up? I've been wrestling with this for a few
days now and can't find a solution.

View 2 Replies View Related

Log UserID Of Person Who Last Made Update In External File Links

Oct 2, 2006

Is there a way to log the userid of the person who last made an update in the external file links?

View 4 Replies View Related

Update Of Links In Formula For All Cells

Oct 20, 2006

I have cells (range named Formulas) in a worksheet A (wsA) linking to another workbook. This workbook has several sheets, each a different forecast. From a Radio Button Group on wsA I would like to choose forecast, FC1-FC3, and then all the cells in the range Formulas would be updated with the choosen FC.

I would like a VBA script to handle this:When the workbook is activated the present Forecast in the Radio Button Group should be stored as a variable (oldFC)When I choose a new forecast, the value should be stored in a new variable (newFC)I then want to loop through the whole range (Formulas) and update each cell and it's part of string value to the newFC

Before update:
wsA cell N3='H:[5500-plan-06.xls]FC 3'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 3'!N$17

When new Forecast is choosen this should be update to:
wsA cell N3='H:[5500-plan-06.xls]FC 1'!N$16
wsA cell N4='H:[5500-plan-06.xls]FC 1'!N$17

View 2 Replies View Related

Get Links To External Files

Dec 6, 2006

I want to link data between two files, which are always in the same position relative to each other, but can be copied to other locations. Here is an example:

D:Job 1SourceJob Info.xls
D:Job 1SlaveClient Form.xls

Cell B1 in Client Form.xls refers to Cell A1 in Job Info.xls. This link is created when both files are open. When Job Info.xls is closed, the reference in Client Form changes to 'D:Job 1Source[Job Info.xls]Sheet1'!$A$1.

Then, I copy and save Job 1 as Job 2. Thus, the second set of folders are: ....

View 7 Replies View Related

Name Manager And External Links

Apr 25, 2007

I have been trying to save myself alot of time by using the name manager and an external link to another workbook without success.

The real directory name is very long. There are 200+ different markets like forex1
and 25 different "Locations" that refer to the same things

Name Manager name Value
Directory ="C:DemoVP["
Forex1 ="Australian Dollar Japanese Yen Cash.xls"
Location1 ="]Sheet1'!A2"

Now I am trying to add these 3 thing together to get an external link without success with the following =Directory&Forex1&Location1

I get this below, but no external link 'C:DemoVP[Australian Dollar Japanese Yen Cash.xls]Sheet1'!A2

View 2 Replies View Related

Update Links And Click Update, Returns #VALUE!

Jun 21, 2007

I have a vlookup to another workbook. It works fine if both workbooks are open. But if both are not open and I open the workbook with the links and click Update, #VALUE! returns. I have attached the two files. I don't think it is my formula, but here it is anyway. =IF( COUNTIF([Tempozgrid.xls]June!$A$52:$A$83,A3),VLOOKUP(A3,[Tempozgrid.xls]June!$A$52:$L$82,12,FALSE),0)

View 2 Replies View Related

How To Remove External Links From Formulas

Jun 9, 2014

I have recently come across an issue I have never seen before.

In the attached xlsx file, I have formulas which are trying to reference an external file (C:UsersDELLDownloads[test.XLSX])

How do I delete the external link without losing the formulas.


View 2 Replies View Related

External Links Cause Program Crashes

Jan 4, 2009

My system is Vista Ultimate with Office 2007. I use Norton Internet Security 2007 with Microsoft Office Automatic Scan disabled.

For many years I have used a spreadsheet which links to an external data source - eSignal. eSignal provides real time updates to the spreadsheet.

The problem occurs after I click the link to enable automatic update of links.

About 2 out of 3 times when I load this spreadsheet Excel has a problem with indigestion and closes.

Here is the problem report:

Problem signature
Problem Event Name:APPCRASH
Application Name:EXCEL.EXE
Application Version:12.0.6331.5000
Application Timestamp:48fa2869
Fault Module Name:EXCEL.EXE
Fault Module Version:12.0.6331.5000
Fault Module Timestamp:48fa2869
Exception Code:c0000005
Exception Offset:000453a2
OS Version:6.0.6001.
Locale ID:1033

Extra information about the problem
Bucket ID:1048410467

Does anyone have any idea how this error can be avoided? (Even with a clumsy workaround?)

View 12 Replies View Related

Moving A Workbook With External Links

Nov 23, 2009

I want to move the workbook I have created to a different folder on the same drive. The workbook is pretty big and has loads of links to other workbooks. If I move it will all of these links fail and if so will I have to manually reset them all? I guess I am asking if there is specific method of moving workbooks which will automatically change the links to the cope with the new location?

View 3 Replies View Related

Sumifs Formula With External Links?

Sep 12, 2012

I have some sumifs() functions in cells in a workbook that contain external links in them:

=SUMIFS('C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$E$54:$E$88,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$C$54:$C$88,$G50,'C: empForAuditor[Budget Employees 2013 (0055521).xlsm]Labor Summary'!$A$54:$A$88,H$49)

The formulas work fine if the external file is open. However if the linked file is not loaded and I load the file with the external links, as soon as the file with links is calculated, some of externally linked cells turn to "#VALUE" errors. I have a need to have only certain people see the employee file that is referenced above but the others need to update the file with the external links and not have errors in cells.

The really weird thing is that some cells yield the #VALUE error and some don't, even when both have SUMIFS() functions in them. Even cells with the Exact same formula in them will sometimes yield #VALUE and other times yield a real value.

View 3 Replies View Related

Several Links To External Workbooks That Cannot Be Found

Nov 15, 2006

I have several links to external workbooks that cannot be found...

I have deleted all Name references that posses a ref#

Also I have tried to change the source to the workbook that I am using, but I keep getting a message that says invalid reference to external workbook! In other words it won't let me change my source

I have several work books that I want to change there source, or even get rid of because I can't update them anyways and I don't think it matters if they are updated!

View 9 Replies View Related

Change/Edit External Links

Dec 12, 2007

Is it possible to update external links on a worksheet via running a macro?
I have a worksheet where some cells have external links to other files, and every month I have to scroll through and do the typing in order to have everything up and running.
A typical cell has formulas like =[E:ReportsAug-2007.xls]Data!F25
Another cell =[E:ReportsAug-2007.xls]Data!F43
and so on.

Every new month I actually have to replace all [E:ReportsAug-2007.xls] occurrences with for example [E:ReportsSep-2007.xls]. So, is it possible to have a macro which opens a textbox of which i can type the new month 'Sep', so that when the macro runs I save all the typing?

View 3 Replies View Related

Name Manager - Edit External Links Automatically?

Mar 21, 2014

how do I edit external links automatically in name manager?

I got external links like


It is easy to replase manually few links, but what about few hundreds...

How do I replace from




automatically ?

View 1 Replies View Related

Dynamic Path Names For External Links

Jun 18, 2008

I am designing a spreadsheet for work.

each of the people i work with have their own workbook with their name as the title, John Smith.xls.

There are about 20 of us on the team with their own workbook with the same design.

We record information on these weekly and so there are 20 files in the folder for Week 1, 20 in Week 2 etc.

I want to make a master spreadsheet with will collect all of the information on these sheets and list them on a master spreadsheet every week.

So the external data i am linking to is found in a location like

='C:SpreadsheetsWeek26[John Smith.xls]Sheet1'!$A$1

Here is my problem,

I need the 'Week 26' and the 'John Smith' to be changeable and preferabilly linked to a cell using the INDIRECT formula so I can change all references to Week 26, 27, 28 etc and also change the workbook name it is referencing.

I have attempted to do this in the following way

I broke down the pathname to seperate cells

Cell 1: 'C:Spreadsheets
Cell 2: A reference linking to a cell containing value: Week 26
Cell 3: A reference linking to a cell containing value: [John Smith.xls]
Cell 4: Sheet1'!$A$1

I then used the CONCATENATE formula to link these into a single cell as a text string.

I then tried to use INDIRECT to link to the filemane in the CONCATENATE cell.

This did not work.

I need a way to make parts of the filename variable and linked to one cell.

I also need to do this while the other workbooks are closed.

I have found some information on INDIRECT.EXT which as far as I can make out involves installing mods to Excel to make these work, I cannot do this as the spreadsheet will be used across our network and will eventually be used by thousands of users.

View 11 Replies View Related

Variable File Name For Creating External Links

Apr 10, 2013

I am working on a research project of which details cannot be disclosed. I basically have many worksheets which each contain 3D positional data and I am plotting overlaid graphs using multiple worksheets. I have gotten them to work where I manually enter the names of the worksheets, but I am trying to use the CELL("filename") command to get the current worksheet's name, then using a substitute command to change certain fields, then I would like to use the string name created by those functions to call a global variable in another worksheet which defines a range. I will try to give an example.

Say I have 3 worksheets for arbitrary measurements:
1) filename 1mm
2) filename 2mm
3) filename 3mm

I would like to graph all 3 sets of data in worksheet "filename 1mm", using the fact that everything in the filenames are the same except for the 1/2/3mm part. I have a template of which I will be copy/pasting data from numerous data sets and then each worksheet has defined names "X_vals" and "Y_vals" which give me dynamic ranges for the data I wish to plot from that worksheet.

When I type the names manually--i.e. " 'filename 2mm'!X_vals" everything works fine, but when I try to create the exact same string dynamically using indirect/substitute/left/right etc, it does not let me do it. I am able to create a dynamic string with a range, such as " 'filename 2mm'!C10:C100" but then when I change it to " 'filename 2mm!X_vals" it just gives me #REF!.

I guess my question is just is there any way to use the indirect function to create a string name dynamically which references a defined name in another worksheet? If not possible with just simple excel functions, is there a way to do this with a macro? I am not exactly familiar with VBA in excel, though I know how to run macros.

View 3 Replies View Related

Disable External Links When Creating New Sheet?

Mar 7, 2012

I have VBA code that creates three reports based on fields a user chooses. When the user clicks the button to create the first report, links are built to the files which contain the fields they chose. There are approximately 15 files that need to be linked in every report.

When the links are built, referencing the external files, the system is extremely slow.

I have Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual.

Despite these settings, the links pull in updated values.

Also, to create the last two files, I do a FIND/REPLACE to change the cells referenced in the links, causing it to choke.

would get the links in place without updating each one as it is created?

View 1 Replies View Related

External Links Break After Source Workbook Is Closed

May 21, 2013

I'im using an index - match - match formula with links to an external file.

Everything works fine until I close the source workbook. At that moment the liks break. The error I'm getting is #REF!.

I suspect the reason is that the source data are in form of an official table. I tried some formulas with data in the same workbook but outside the table and they keep working after the source file is closed.

I really want to keep the source data in a form of an official excel table.

View 6 Replies View Related

Links To External Workbooks And Server Drive Selected?

Aug 7, 2013

I have a workbook that is being used by many individuals. Within it contains links to an external workbook on a server. Currently the links in the workbook map the server to drive "T".

If a different user maps the same server to another drive letter, I assume these links need to be updated with the drive letter he/she is using for that server?

View 1 Replies View Related

Adjusting External Cell Reference (Links) In Excel

Aug 7, 2013

The goal is to have a data validation drop down list that adjusts the File.xls name portion of the external cell reference (example given below) and return the result of the cell I am searching in the new file after updating my links.

I am trying to set up an external cell refence within my workbook for example:

When I have tried setting the equation to pull from the drop down list in cel A1 for example:

It only shows the new file path name and not the value in the new sheet--even after updating links.

I am also attempting to do this using the my network places file path instead of the letter drive in my computer.

View 2 Replies View Related

Excel 2010 :: Locate And Eliminate Links To External Sources Referenced By Worksheet

Feb 15, 2013

Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.

Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.

The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.

View 1 Replies View Related

Update Links When There Are None?

Jan 27, 2009

i have a complex workbook. Recently, when I open it, it asks me if I want to update links. I never (that I know of) established any links to other workbooks. I see that a workbook is referenced in the "edit/links" menu, but I can find no way determine which cells, graphs, etc, in my workbook are subject to this link. I would like to get rid of this useless and bothersome issue.

View 5 Replies View Related

Links Do Not Automatically Update

Sep 11, 2013

I have a dashboard (CW Dashboard.xlsx) that is linked to 3 main spreadsheets. In each main spreadsheet, there are 5 tabs. One tab contains raw data, the other 4 tabs contain tables: 2 tabs with formulas based on the raw data tab, and 2 tabs linked to 20 other spreadsheets (for now). I separated the raw data into 3 spreadsheets because Excel was calculating way slower than my patience would tolerate. All spreadsheets are Excel 2010. All spreadsheets reside on our network drive.

The links update as expected on the 2 tabs that are linked to the 20 different spreadsheets in each of the three main spreadsheets.The CW Dashboard does not update. I get #REF! in each cell (although when I click in the cell, the link shows no error). I am using VLOOKUP and HLOOKUP in this spreadsheet. I can of course open the source documents from within the Dashboard and the links then work as expected. However, I would prefer the eventual users of the Dashboard not have to open 4 s'sheets.

View 2 Replies View Related

Update Links To All Spreadsheets

Jan 13, 2010

I'm hoping to find a way to update links on all spreadsheets going backwards.

ie. s/sh 5 is linked to s/sh 4
s/sh 4 is linked to s/sh 3
s/sh 3 is linked to s/sh 2
s/sh 2 is linked to s/sh 1

Is there a way of opening s/sh 5, and having it automatically update s/sh's 2,3 and 4 (1 has no links), rather than just updating s/sh 4, as would be the normal functionality. Currently I need to open 2, then 3, then 4, otherwise changes to s/sh 1 do not flow all the way through to s/sh 5.

View 3 Replies View Related

Update Links Object

Jun 27, 2006

I have a macro that creates links to another workbook and then changes the setting for the links update to "xlUpdateLinksNever". I wrote this macro in Excel 2003. I am now testing across different excel platforms and this errors out when I try to use it in excel 2000. I'm assuming this is because excel 2000 does not have the same ability to ontrol the links.

View 6 Replies View Related

Update Formula Links

Nov 3, 2006

I have a sheet with formula in various cells that are referenced to a sheet that doesnt initially exist. For example one formula on the sheet is: =If( 'WF Tracker 2008'!$D$5=AN$4,INDEX('WF Tracker 2008'!$B$6:$P$10, MATCH($E36,'WF Tracker 2008'!$B$6:$B$10,), MATCH(AN$7,'WF Tracker 2008'!$B$6:$P$6,)),IF(AN26="N/A",AN31,"N/A"))
and initially, sheet 'WF Tracker 2008' doesnt exist so the formulas all produce #ref! values which I expect. My problem is that I Run a macro that creates the sheet 'WF Tracker 2008' but the formula dont want to refresh/update unless I individually go into each cells formula in the formula bar and hit return.

View 6 Replies View Related

Update Links Across Workbooks

Jun 6, 2007

I have a Vlookup in a shared workbook looking in another shared workbook for the data I want. I open the file and click "Update Links" but I then get the following message box:

"This workbook contains one or more links that cannot be updated.
-To change the source of links, or attempt to update values again, click Edit Links.
-To open the workbook as is, click Continue.

I know this message is trying to tell me it can't find my links, but the path/workbook/ sheet are correct and the numbers are getting updated. I'm worried the message will deter the other users of this workbook from allowing the use of the vlookup.

View 9 Replies View Related

Update Links Without Prompt

Mar 18, 2008

I'm using code which forces users to enable macros prior to being allowed into a file. Then I ran into an issue of needing this Shared Workbook to update links, which you can't do in the workbook_open event and I do not want users prompted to update or not. So what I've done is made a 'skeleton' file to force the macro enable and then open up the real file with updated links. Once the real file is opened I want that skeleton file to close. For some reason, once the file closes the code halts. "Forcemacroenablemaster" closes, but nothing runs after it. If I comment the close line out everything after runs. This code is in my "Forcemacroenable" file:

Private Sub Workbook_Open()
Call ShowAllSheets
Workbooks("Forcemacroenablemaster.xls").Close False
MsgBox "Test"

Call ValidSetup
End Sub

View 3 Replies View Related

Copyrights 2005-15, All rights reserved