Value Of A Cell In An External And Variable Workbook
Mar 16, 2008
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.
View 9 Replies
ADVERTISEMENT
Jun 29, 2006
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
View 8 Replies
View Related
Nov 24, 2011
I've got a field where I enter the current month, from this I derive a file path which is stored in A4.
ie: A1 = '01/11/2011' (UK Date format)
A4 = '\myserverdirectory20111111MyFile.xls'
What I'm trying to achieve is to then have a link in cell A6 that will link to the file in A4. I can specify my cell reference as this will never change, just the file path.
So, if I changed A1 to '01/12/2011' my A4 changes to '\myserverdirectory20111211MyFile.xls'
I'm trying to set A6 to something like ="&$A$A4&"!B1 to get the value of B1 on my referenced sheet.
View 1 Replies
View Related
Apr 21, 2008
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.
View 3 Replies
View Related
Feb 24, 2014
I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?
In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...
Mary
='X:Anderson CorporationEmployees[Work Hours]Mary'!$F$42
Joe
='X:Anderson CorporationEmployees[Work Hours]Joe'!$F$42
Frank
='X:Anderson CorporationEmployees[Work Hours]Frank'!$F$42
Michelle
='X:Anderson CorporationEmployees[Work Hours]Michelle'!$F$42
Sam
='X:Anderson CorporationEmployees[Work Hours]Sam'!$F$42
View 1 Replies
View Related
Oct 21, 2008
Using Microsoft ® Office Excel 2003 (11.8220.8221) SP3, I started with a sheet, Sheet7, that pulled the first non-blank error message found in a common cell (i.e., A7) in Sheet3.1, Sheet3.2, ... , Sheet3.15, Sheet3.16. All of these sheets resided in a single workbook, and worked correctly using the following VBA code snippet:
View 5 Replies
View Related
Apr 13, 2013
I've been trying without success to hyperlink a shape in one workbook that refers to a website contained in a second workbook cell.
The hyperlinked shape is in the current workbook and the website address is in a workbook called "data source.xlsx", sheet 1, cell A1.
My last attempt was this - ('C:UsersLouiseDocumentsMA[data source.xlsx]Sheet1'!A1)
View 3 Replies
View Related
Feb 27, 2014
I'm wondering if there's any way to put a variable for a cell address in an external reference formula. Basically, I'm creating an abridged report of customer data that takes a single customer's info from 2 other workbooks on a networked drive and compiles the chosen cell data into one worksheet summary of their info. The base external reference formula looks like this:
='F:Projects[CustomerDB.xls]Sheet1'!VariableCellGoesHere
I want to know if there's a way I can make the formula use a variable cell address that I can just type into another cell to tell it which row I need to grab info from. Generally, all of the customer data I need is spanned across multiple columns in one single row. The formula above appears multiple times in the sheet I'm trying to make. The first is for "Name", the next is for "Address", next is "Phone Number", etc. So for instance, if I want customer data from the main spreadsheet and that customer's info is on row 355, my first few formulas are:
='F:Projects[ProjectDB.xls]Sheet1'!$A$355
='F:Projects[ProjectDB.xls]Sheet1'!$B$355
='F:Projects[ProjectDB.xls]Sheet1'!$C$355
Is there a way I can maybe have a designated cell where I just enter the row number I need (e.g. 481) so the formula sees that number and plugs it into the cell reference above resulting in the formulas adjusting themselves to ='F:Projects[ProjectDB.xls]Sheet1'!$A$481?
View 9 Replies
View Related
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
Nov 29, 2013
I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.
View 2 Replies
View Related
Feb 5, 2008
I have a list of objects in column A
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
View 9 Replies
View Related
May 24, 2007
Say cell C5 contains the name of a project (workbook). eg
C5 = "[Project1]"
In my current workbook, I want to find the value in cell B8 of Sheet1 of the project shown in C5. If I wrote this directly, it would be "='[Project]Sheet1'!B8"
But the name of the project is a variable shown in cell C5 (as explained above). So I need a formula that will find the value in cell B8 of sheet1 of the project shown in C5.
View 7 Replies
View Related
Jul 31, 2014
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.
external links.png
View 5 Replies
View Related
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
Dec 14, 2009
I'm having trouble working this around, i use the following code in one of my Workbook sheets.
View 10 Replies
View Related
May 2, 2008
Is it possible for the INDIRECT function to be tweaked so it updates even when the external workbook is closed?
View 13 Replies
View Related
Mar 26, 2009
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....
View 9 Replies
View Related
Jan 22, 2007
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)....
View 9 Replies
View Related
May 16, 2013
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.
View 2 Replies
View Related
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)
[Code]....
View 5 Replies
View Related
Oct 26, 2008
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)"
View 3 Replies
View Related
Jun 12, 2009
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.
View 3 Replies
View Related
Jul 22, 2009
I'm trying to create a workbook that opens the windows explorer and let me select a workbook, then in one worksheet merge all the active sheets present in one.
View 2 Replies
View Related
Sep 16, 2009
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.
View 10 Replies
View Related
May 2, 2009
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.
View 9 Replies
View Related
Jun 19, 2006
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
Application.ScreenUpdating = True
View 2 Replies
View Related
May 6, 2007
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.
Program_1.xls code
Sub Program_1()
MsgBox "This is program #1"
Application.Run "Program2.xls!Program_2"
End Sub
Program_2.xls code
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?
View 9 Replies
View Related
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
Nov 23, 2011
I am importing external data to "Workbook A" from the source workbook, "Workbook B." Workbook A is set up to refresh every 15 minutes. Workbook B is shared. I am recieving an error saying either, the 'file is in use', or 'the file is locked,' depending on the senario.
Scenario 1:
1. I open A and enable data connections.
2. I try to open B, but receive the message: '(File location...) is in use. Try again later.
Scenario 2:
1. I open B.
2. I open A.
3. I go back to B to edit information.
4. When I try saving the information I get the following message: 'File is locked. Try the command again later.'
I need to be able to have A open and periodically refreshing. I also need to allow users unhindered access to B at the same time.
View 1 Replies
View Related
May 14, 2012
I have this micro enabled workbook and I want to protect it from being copied on other sources.
View 1 Replies
View Related
