Updating Links All At Once
Apr 11, 2008
if it would be possible to update all the links in a workbook at once. For example, say I have a worksheet which is linking to a file named something like 4-6-08 (1) and 4-6-08 (2) and so on with the number in brackets being from 1-21. Now say I have the same thing for 4-7 and 4-8 and 4-9. Now I want to change the links to point to 4-10,11,12,13 instead of 6,7,8, and 9. But still there are 21 copies of each sheet. Is there any way to change all the sources at once so all the 4-6's could become 4-10's and 4-7's to 4-11's?
View 12 Replies
ADVERTISEMENT
Mar 23, 2007
I have several Excel files saved on a network drive at work. We run cost studies and have numerous links mixed in with these files. My question is, when we change data in one sheet, it doesn't update the other sheet where we have it linked. When you click on the address for the link you get, (\fileshareN:Raetc...) If I remove the \fileshare and leave it as N: the links update. What am I doing wrong here? It automatically adds this \fileshare piece when I add the link. When I have removed it and close it, the extra information is added and I am back at square one.
View 2 Replies
View Related
May 4, 2007
if it was possible to write a macro that will update a link to a file that has the same file name but now lies in a different folder/directory path. I have about 50 spreadsheets that link to this file which has now been moved.
View 9 Replies
View Related
Oct 26, 2006
I have a workbook (we'll call it Mainbook)that has links to files on a shared drive (call these subbooks). For some reason, unless the "subbook" files are open, my links in "Mainbook" will not update.
View 9 Replies
View Related
Jan 23, 2009
I have a master price list that will have items added or removed from it occasionally. I have sixty workbooks that contain detailed data on mixes of multiple products from the master price list. All the corresponding prices throughout the sixty workbooks are linked to their specific slot on the master price list. The problem: if the sixty workbooks are closed and anyone adds a new row to the master price list, when I open any of the workbooks the values update but the referenced cell does not.
So if the price for Product 25 is located in cell K118, and two products are added in above K118, when I open any of my mix sheets that contain Product 25 the linked cell should be to K120. But it's not doing it that way. It is holding on to the referenced K118 cell and updating the value to the wrong price.
I've played around with the VLOOKUP function but haven't been able to make it work. Linking the cell as I have has worked great, if I could just get it to adjust the reference as new rows are added to the source workbook.
I've figured out that it will update the workbooks when I add a row if all that are required are open when the addition is made. But my boss will not open sixty workbooks every time a change needs to be made to the master price list, which he controls.
View 8 Replies
View Related
Mar 4, 2009
My links are not automatically updating on my worksheets. I was recently working on a sheet that linked to a different sheet in the same workbook. I deleted some cells by dragging other cells onto them, but when I went to the sheet that was linked to this sheet, the old information remained. When I clicked the cells that should have said "#Ref", they said =Sheet1!#REf but still had the old information showing until after I hit F9.
View 2 Replies
View Related
Jul 14, 2010
In my worksheet i use a macro to produce my results which runs perfect.
Later i copied my range and pasted as picture link in another sheet.Since then the macro takes a long time to run. I suppose while running the macro xl continuously updates the picture slowing the macro.
i already set calculation as manual but no use.
View 3 Replies
View Related
Oct 8, 2007
I have a large workbook 45MB with 40 sheets each containing a section that links to the server to update itself when the workbook calculates. With that being said it's impossible to have the workbook on Automatic calculate because the update process can take about 7-10 minutes so its set to manual. However, I would like to prevent the links from updating (its one link that is used to update the file) by possibly a macro that runs when the workbook opens and prevent the update link from running when I press F9 to manually calculate.
View 2 Replies
View Related
Apr 4, 2006
I've got a workbook in which there're many formulas linking to other workbooks. Coz these other workbooks are frequently updated, so the calculation (update) in my workbook is painfully slow and time consuming. I wanna improve this, maybe by VBA or other way instead of using link like this. Anyone can give me some hints or way how to improve this condition???
View 7 Replies
View Related
Sep 12, 2010
I and my organization have many files created using Excel 2003. I have now upgraded to Excel 2007 and soon I discovered a huge problem.
- When opening a file using Excel 2007 that was created with Excel 2003 the links in that file is automatically updated even though I get the edit links dialog and choose not to update.
After searching for a solution on the Internet I realize that this is a problem that exist when Excel opens a file created in an earlier version. Microsoft has no solution.
I need to be in control of which links that are updated and not.
Going back to excel 2003 is not an option. Saving the source files in Excel 2007 is not an option.
View 5 Replies
View Related
Sep 12, 2013
I'm linking some data from 2 workbooks in the same folder (using excel 2013)
I have a main rota that works great, called "Main Rota.xlsm" I then have a separate basic file called Email Rota.xlsx without all of the bells and whistles of the Main Rota, purley made to email out to staff.
I store both files in the same folder "Site Rota" and it works fine.... until I rename the folder or move the folder to my laptop.
The link in the Email Rota looks like so to start (calling cell A3 from the Main Rota) ='[Main Rota.xlsm]Planning Rota'!A3
As I said it works fine until I move the folder onto my laptop the link then changes to
='D:DesktopSite Rota[Main Rota.xlsm]Planning Rota'!A3
And I have to keep updating the link, I have read online that it isn't supposed to work like this and its supposed to read the file "cleverly" in the same folder ?
I would ideally like to keep both files in the same folder and to update from visa versa without having to update the links if I move it from PC to laptop with I do daily .
View 1 Replies
View Related
May 28, 2007
Does anyone have any code to put in Workbook_Open module that can get rid of this message that says "The workbook contains automatic links ......." and default the selection to Yes when asked if I want to update all linked information?
I tried this but it doesnt work ...
Private Sub Workbook_Open ()
Application.DisplayAlerts = False
.........
End Sub
View 4 Replies
View Related
Jul 24, 2007
Currently I am using VBA Code below to Stop Update links prompts
Private Sub Workbook_Open()
Workbooks.Open "C:TempBook2.xls", UpdateLinks:=False
End Sub
Is there code I can use such This Workbook so that I can save this file as another name and it will work? Biz Auto Merged Post;Dear All, I have found VBA code that works.
Private Sub Workbook_Open()
Workbooks.Open ThisWorkbook.Name, UpdateLinks:=False
End Sub
View 3 Replies
View Related
Jun 22, 2012
I have an unwanted link and I can't remove it. I have tried through the Edit Links dialog box, searched for [ in formulas throughout the workbook and gone searching manually for the reference but to no avail.
View 9 Replies
View Related
Jun 15, 2009
I've been having a small problem with my UDFs. I'm sorry if it's already been asked but english isn't my first language and I don't know the proper search key works to find my problem.
Basically, I've been programming functions destined for a single workbook so I don't use addins. If I write a function called J13F and I try to write that in one of my excel sheets, it generally doesn't work. Excel makes me write it like this:
='myfilename.xls!'J13F.J13F(Sheet!F21)
However, sometimes when I close then open my file, this won't work and excel will make me use my function normally: J13F(Sheet!F21)
I'd like to be able to use the function normally in every sheet in my workbook, how can I do that?
View 7 Replies
View Related
Feb 7, 2010
I am using several spreadsheets which are currently linked to specific cells in other workbooks. These are updated through dating the links whenever needed. However I would rather not use formulas in the cells at all and only have value updated by using the link function within VBA. I cannot find how to do this though.
Just to reiterate. I currently have cells that have"='\server01main_foldersubfolderDocument.xls" for example. The formula syntax is not exactly right but in the spreadsheets it is so you get the idea. These links are updated (when the workbooks are not open) when the links are update via the edit menu or a macro which does the same thing.
What I want to do is have no formula in the cells (only values) and use the feature that takes the value from the other workbooks but in VBA exclusively. This is what I have not been able to find out how to do.
View 5 Replies
View Related
Sep 9, 2009
I've created a spreadsheet which pulls data from about 5 other spreadsheets via links in the formula.
Unfortunately I am having trouble with these updating. I have to open the 5 individual spreadsheets in order for the data to pull across otherwise I get VALUE in every cell in the amalgamated spreadsheet.
Any thoughts why this should be the case? I've tried it in the past and it's always worked fine.
View 11 Replies
View Related
Feb 20, 2014
I just want to open some links in a new tab in a browser with a single click in a button.
For example:
[URL]
then in a new tab
[URL]
View 1 Replies
View Related
Nov 10, 2005
I have very large sections of data which I need to transpose as links.
Problem is that the copy/paste links function cannot be followed up with the
copy (the linked cells)/transpose option, without having the transposition
screw the links up.
View 9 Replies
View Related
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
Mar 4, 2008
I've got an excel file with LOTS of links... it's a sort of 'dashboard' that draws in data from all the individual data files in the company.
Recently, when I open the file it's telling me "this file contains one or more links that cannot be updates".
So... as I usually would, I use my 'find links' add in to find anything with a #REF in it... nothing.
Go into edit links and manually update each link... and they all update with no issue.
I just cannot find anything that's broken!
View 9 Replies
View Related
Jun 26, 2009
Is there a simple code I can add to the end of my macro that will break all existing links in the active workbook?
View 9 Replies
View Related
Jul 21, 2014
link a date within a Excel sheet to the Calander in Outlook so a reminder can be triggered on said date.
View 2 Replies
View Related
Jul 29, 2014
I'm looking for a formula that links different conditions together.
For example, I'm entering the formula into G9 and I want the formula to be if G7 is equal or greater to G16 then the answer should be 30, if not then the answer is 0.
Simple enough I know this is my formula to use "=IF(G7>=G16,30,0)" but I want it to look past that.
Basically I want it to say if G7 is equal or greater to G16 then the answer is 30, if not it's 0 BUT if G7 is equal to greater than G17 my answer is 60 BUT if my answer is equal to or greater than G18 my answer is 120.
G17 will always be a larger number than G16, and G18 will always be a larger number than G17.
View 3 Replies
View Related
May 21, 2014
I have a link
='S:2014DomesticATL[ATL-BP Eval-2014-04.xls]Business Partner Evaluation'!F$13
Now i have to compile 3 years of data for 200 airports so changing the month and year for every airport would be a challenge. I can do Replace all and change the year and month but instead of that i was thinking if i could have the year and month on one row and connect the links to that cell so that whenever i drag the cell my link changes
im looking at
2014DomesticATL[ATL-BP Eval-",D2",.xls]Business Partner Evaluation'!F$13
where in D2 i will have 2014-04 . But this formula doesnt seem to work. Is there anyway i can do this ?
View 3 Replies
View Related
Sep 10, 2009
I've read a bit on the web about there being a bug with the 'edit links' feature. How/Can I get this function to work?
View 6 Replies
View Related
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
Dec 18, 2013
For three years I've created a workbook (A) that is dependent on another workbook (B) I upkeep. When I open (A), I Edit Links and update the location to my newest weekly file to map from (B). For my 2014 (A) book, I did the same process, but when I update the values, all the links break. Only when I open up (B) with (A) will the values appear.
As a short term solution, I saved (A) with the values in there and disabled the prompt to update values, so others can see the data. This isn't a feasible long term solution since updates will be made weekly, and not everyone will want to open up (B) just to update (A).
View 9 Replies
View Related
Mar 5, 2014
I have an Excel file which extracts data from other excel files (the other excel files are named 1.xls, 2.xls, 3.xls, etc.). So the formula in the cell is for example: ='C:[1.xls]Sheet1'!A1
Is there a way to let Excel fill in the next excel file address for the next cell? So that i would get this:
='C:[1.xls]Sheet1'!A1
='C:[2.xls]Sheet1'!A1
='C:[3.xls]Sheet1'!A1
='C:[4.xls]Sheet1'!A1
etc..
Because i have about 500 excel files, and otherwise i have to change the file number manually...
View 2 Replies
View Related
Feb 3, 2014
So I got a list of months in a drop down box that when selected goes to the appropriate month tab that works great. But what I want to do is hide those month tabs that are in the drop down list. I'll add another drop down list with the other tabs to reference back to all the other month tabs so the user can move around. Only problem is when I do hide them they are unavailable meaning when selected it doesn't go the the appropriate tab that's hidden. I want to hide them from the user and still be able to go to them, I'm not worried about the user breaking the sheet. See attached example.
There is VB code in the "Home" sheet. Tab Links.xlsm
View 2 Replies
View Related
