I have 3 sheets: 1: master entry sheet that fills data in 5 different excel docs. Then there is another doc that aggregates data from the 5 and presents totals on the data. I am trying to get the end result without having to have 7 different excel files open, but when I enter into the master entry sheet, the data doesn't seem to be "pushed" up to the 5 different docs until each one is opened up. Obviously, the master aggregator is not updated since the 5 don't have the new data. When the files are all opened at the same time, the data flows perfectly, I'm just trying to find a way to avoid having to open the 5 docs in the middle of the process.
I want to auto update ( refresh ) an external feed containing odds from Pinnacle Sports website, at 30-60 seconds intervals. Here is the link : [URL] .....
I would also want to apply some formulas to other columns in excel, but mainly I would want to know where is the change in odds. For example if Bayern Munchen has the odds 2, and after the refresh the odds dropped at 1.9 I would want to see the difference in another cell.
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
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?
I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:
As you would normally use indirect formulas so the cell references don't change. Which that is what I want in the end, but I need to copy them to an indefinite number of cells first and would like to not do it by hand. I have found some solutions to similar questions/problems but cannot figure out how to make them work for me. So, what I am looking to do is this... (I have also attached the spreadsheet for reference)
I have gotten the information in columns A through F on the first sheet to update as rows are added, moved, deleted on the second sheet using Indirect range. Also, I could do this for Column I (Copmleted Proj. Avg. Terminations) but I would have to do it manually (as I began doing in I3, I4 & I5) but that would be time consuming. So I am hoping there is a way I can copy the formula down the cells are updated for the initial copy but then don't update if the referenced cells are moved or deleted.
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 Worksheets("DataSource").Activate Workbooks("TargetWorkbook.xls").Activate 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.
In an Excel sheet i have set up a link to an Access database query (select query) using the Import External Data method. The data is store sales by week, arranged in a pivot table layout (stores as rows, weeks as columns). As time goes by, the database will be updated for new weeks sales, and i would like to refresh the data and the new weeks automatically appear in the Excel sheet, in subsequent columns.
At the moment, the only way i can seem to get this to work is to open up the link in MSQuery and manually select the new weeks from the Access query. i have set the parameters to overwrite cells with new data rather than insert new data. i'm sure there is a way to do this, as it feels like it would be a common thing to want, but i can't seem to find an answer anywhere!
I have a pivot table created from a cube of data not within the file I am working in. I have created a macro that will update, but only for the specified timeframe. I have a separate worksheet within my file that will allow a manger to specify what timeframe he wants to see. Can I edit a macro to read from a source that is different than where the data is being pulled from?
I want to select that list or part of it then run a VB module that references each item in the selection and compares it to a master list in a separate workbook that contains both the object and an additional value, and display that additional value in column B of the original workbook against each object in column A
Just wondering if there is a way to indirectly reference a workbook in a formula?
For example, suppose A1 has the path/filename of the workbook I want to reference.
So in another cell I want to do, say, a VLOOKUP(A2,A1,5), where A2 is the lookup value. This way, if I have multiple workbooks I want to look at, I can specify them all in various cells, and simply have the formula look at the right cell to perform the lookup.
I've played with INDIRECT and get something close if I do
but that just returns the contents of A1 in the target sheet.
I have a document (unfortunately I cannot attach it) in Excel 365 that each time I open it I get the usual prompt that it is calling an external link. As much as i'd love to hide the popup I need to find the link to solve to problem but I'm at a loss.
Simply breaking the links doesn't work for some reason, so I've tried updating the source to reflect the current file. No luck there.
I've searched the workbook for the name of the link in question, searching for all formula for name itself as well as variants of "[" and ".xlms".
I don't see anything in the name manager referencing that external file.
I don't recall if I copied from that particular document, I may have as it was a duplicate file. I tried looking over the cells I believe I copied from it but didn't see the reference.
I've installed Kutools and Bill Manville (MS MVP) FindLink Tool. Both of which says there is no external link.
I've looked at the compatibility checker which basically just says, yes there is an external link but give no insight on how to address.
I've tried a couple of macros, some of which crashed excel, some of which simply said that there was an external link but not how to find it. In my frustration I forgot which one that was but I'm about to try that approach again and look for that code.
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?
I need to create a formula or code which will return the value of cell d16 from an external and unopened workbook, where that external workbook's file name, directory and sheet names are provided within cells (able to be updated) of the current workbook. I have downloaded the add-in for INDIRECT.EXT but either being unfamiliar with this function or that I'm barking up the wrong tree all I'm getting is #REF!
And to add to my problem, I was hoping to use this formula / code within an excel table embedded in a word document.
I have been trying for some days to copy a module to an external workbook with VBA. I have about 40 workbooks that have the same format. although I developed these sheets. I am not the person who uses them. I have to on occasion alter the code for some reason, its getting tiresome.
I don't think I can use the personal workbook method for this as it is not me using the workbooks. I don't even know some of the people that use them. This courses me problems because of the number of workbooks. I want to be able to maintain the code by having to write it once and propagate it through the necessary files via the VBA.
I have code from C Pearson that copies a module to another workbook.
I have posted this here just in case. Most threads on this subject just direct you here any way....
Once I open a new workbook, how do I make it the active workbook to have a function make changes to it? Problem I am having is even after it is open, the function will only make changes to the workbook containing the Macro (Thisworkbook)....
My indirect formula doesn't work when I have the entire path defined. I have the entire filepath specified (which will never change), and then I have cell A6 with the tab name of the worksheet on the workbook . Workbook 1 named 'Testme' contains the table with range a2:b5 Worbook 2 contains the formula
I put this formula into workbook2 (the tab in the example is named hitab & I put that name in cell B6)
Here is what I have:Does not work even when other workbook is open =VLOOKUP(B6,INDIRECT("'H:CUSTOMERSCurrentClientAMRO and General SuppliesBearings, Motors, PTsSavings Tracking[Testme.xls]"&A6&"'!a2:b5"),2,FALSE)
This DOES work when workbook is open, but not when it is closed (which would seem logical if when the workbook is closed, the formula needs the exact path) =VLOOKUP(B5,INDIRECT("[Testme.xls]"&A6&"!$a$2:$b$5"),2,FALSE)
I can hard code everything except the tab name. Why doesn't Indirect recognize an entire file path and also why does it give a REF error?
- I did a search and found that some people said to use the add-in Indirect.exe. I followed the link and the page was no longer found.
Is there a way to specify the filename for an external workbook that can be referenced throughout the spreadsheet? In my case it would be better if I could change the single reference instead of having to change it throughout the entire spreadsheet everytime I needed to use data from a different file.
I'm trying to summarize some data from an external workbook. The problem is that the worksheet names there are months (Jan2009, Feb2009 etc.) and the sheets rotate (change position) with time. In the summarizing workbook I would like to use a function to refer to (for example) cell A1 on the third worksheet, regardless of the current name of that worksheet. So I might have a cell in the summary workbook that looks something like "=CoolFunction(ExternalWB.xls, 3, A1)"
I want to copy a named range from an external workbook. Currently I am using some VBA copied from a recorded macro that first opend the 2nd workbook and then selects the named range, changes back to the original workbook and drops the copied range into the active workbook.
I would like to find a way to get around having to open the reference workbook, and instead simply point to the proper workbook name and named range.
I have a list that show file names and when the necessary works on these files were completed, but the powers that be want more. I have been asked to add two more columns, one showing when the file was last opened and the other when it was last modified.
Is it possible to do this through VBA without having to open the files that are listed? If so I will run the macro when the workbook is first opened.
when i run this code under a button on a sheet it works fine. but i would like to run when the workbook opens (but i get an error message
On Error Resume Next Application. ScreenUpdating = False Dim wb As Workbook Dim ws As Worksheet 'create new work order number... Set wb = Application.Workbooks.Open("c: empex_ExternalOrderNumber.xls") Set ws = Worksheets("NumberIncrement") 'get new WO number from numberincrement.xls If Err.Number <> 0 Then MsgBox Err.Description & "...help" Else mynewnumber = ws. Range("b1").Value Range("g5").Value = mynewnumber wb.Close End If
I have a workbook (Program_1.xls) which references another workbook (Program_2.xls). I want to close Program_1.xls using code in Program_2.xls. When the Program_1.xls is closed, the code stops.
Sub Program_1() MsgBox "This is program #1" Application.Run "Program2.xls!Program_2" End Sub
Sub Program_2() Source = "C:Documents and SettingsCohenMy Documentsprogram3.xls" target = "C:Documents and SettingsCohenMy Documentsprogram1.xls" Workbooks("Program1.xls").Close savechanges:=False ' ---> code halts here MsgBox "This is program #2" FileCopy Source, target End Sub
How do I get the final 2 lines of code to execute?
I am designing a spreadsheet at work, and need a little assitance. I can't find the answer I need online or here either. Here's the problem: I want to be able to enter data (A 5 digit job number, 80227 for example) into a cell, and then multiple other cells would auto fill with data from outside worksheets. The worksheets are in a seperate folder on my server and named under the same name as the project. (So if I entered 80227 as the data in A1, I would want A2, A3 and A4 to open spreadsheet 80227.xls on the server, locate the referenced cell/s, and insert the info from 80227 into them.)
I can easily reference the cells alone and have them input the data, but this is extremely time consuming since this spreadsheet has all the current jobs our company is working on. I currently am running a macro/VBA that automatically updates all linked spreadsheets without them having to be open, so that is not an issue.