External Workbook Link With Dynamic Cell Reference
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
ADVERTISEMENT
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
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 27, 2014
I have the following formula in my workbook:
=COUNTIF('\caltulfs1DATAProjectsF2014FB-979916 - EQUATE7.0 DOCUMENTATION7.01 DOCUMENT REQUIREMENTS & RECORDS[FB-979916 DSI FORM.xlsm]Customer Tracking'!A12:A86,"past due")
When I open the master file I want my links to automatically update and open the referenced files without having to open the source document for each value. Can this be done
View 3 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
Jul 1, 2013
I have a formula in a cell which is refered to other cell within the same sheet and also to other sheet from other file. say
C1 = A1+B1+data file c1 Data file is another file in other location.
I was trying to break a external reference link (from Data File) from Data _ Edit Link, but it is converting all the formula to value. How is it possible to break only the external link without damaging the formula within the sheet say c1 = A1+B1+26 (value from data file c1)
View 5 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
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
Mar 25, 2014
Say I have Source Workbook X 1, and Destination Workbooks X 4. The Source workbook contains a number of lookup values. I want the lookup values to be maintained in the single Source workbook, and I want this workbook to remain closed when the end users are using their Destination workbooks. It is critical that certain cells in the Destination workbooks have validated data in order for formulas and summarizations to be correct.
I'm happy if the Destination workbooks contain a lookup worksheet (perhaps hidden). In short, I'd like the contents from the Source workbook, Lookups worksheet, to just be replicated "as is" into the Destination workbooks, Lookups worksheet. I then want to do my data validation from that worksheet, which of course is open (data validation appears to only work with open workbooks).
I've investigated the techniques in this link: [URL]
Questions:
1) Are the techniques in that link about the best approach? i.e. an external link plus array formulas?
2) A blank cell in the source workbook (text column but formatted as General) is resulting in a zero in the external link. How can I make the external link exactly match the text as entered in the source workbook?
View 5 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
Apr 14, 2008
My Example
I have Master Book1 which has external links to Book2, Book3 and Book4.
My Formula in Cell B2 is ='D: empxl[Book2.xls]Sheet1'!$A$1
In Cells A1, A2 and A3 I have the text entries 2, 3 and 4 respectively.
Is it possible to use these Text entries to update the Formula I plan to copy down from B2 onwards?
i.e. Formula would read something like ='D: empxl[ Book"A1".xls]Sheet1'!$A$1
View 4 Replies
View Related
Feb 6, 2014
I am using:
Code:
Dim Aname As String
Aname = ActiveWorkbook.ActiveSheet.Range("D2").Value & "Pricing"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Aname & ".xls"
to create a new workbook. I need to copy 2 columns from the original workbook/worksheet to the one being created though. I can get the the original easy enough, but then getting back to the newly created book is proving difficult.
(the 2 columns being copied are filtered to what is needed and I have used the below to copy it, yes I know that is 3 rows but I couldnt get it to copy just E and G at the same time to the lastrow without including F)
Code:
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("E2:G" & LastRow)
.Select.copy
End With
View 7 Replies
View Related
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
Apr 2, 2007
I am building a summary spreadsheet which will bring in a number of values from other spreadsheets located in folders on a network. Each set of external references will return the same cells, but the location and filenames are different. In order to make it quick and easy to modify the file names and locations without having to change the cell references in my summary sheet i want to type the full reference in a cell as text then use this in another cell as part of the external reference...
View 9 Replies
View Related
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:
='[File.xls]Tab'!$C$9
When I have tried setting the equation to pull from the drop down list in cel A1 for example:
="'["&A1&"]Tab'!$C$9"
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
Jul 27, 2006
I need to have a link to data in another file. The file will be in the same directory, however, the name of the file I need to link increments daily. I have a string being generated already with the name of the file I want to link to. I can easily add a cell reference to that string, but how do I use that string value in a link? Or - how do I use quick'n'easy VBA to snatch the required cell's data?
e.g.
Required data is in the file "production 060723.xls", in sheet "Daily Production", in cell "E2". Current file has a string being generated in a cell:
"\server1production dataproduction060723.xls"
The following day, the required data will be in a file called "production 060724.xls", same sheet, same cell. The current file's string data will also update to reflect the needed file. I know that similar questions have been asked here - but none that I saw in my search seemed quite what I need. Even if someone can direct me to the right thread, that would be great.
View 5 Replies
View Related
Aug 28, 2007
I have 6 worksheets that are identical cosmetically. Each has the same drop down box list with the same name (dropdown1), however the cell link on each sheet is different. (ie. sheet1 dropdown1 cell link refers to a sheet ' lookup' cell $K$1, sheet2 dropdown1 cell link refers to sheet 'lookup' cell $K$2, etc.) I have the same command button in each sheet that performs the same task so I wanted to keep one module for all 6. Problem is I set a variable to the cell link in the code, and this has to be changed.
DropDownValue = Sheets("Lookup").Range("K1").Value
Is there any way I can set this variable using a property of the drop down list?
DropDownValue = dropdown1.celllink
I don't want to create 5 more duplicate modules just to change this one bit of code (I only use it this time in the code!). If there is no way to get the cell link value of the drop down, could I possibly put a worksheet change event on the lookup sheet to populate a certain static cell with the value of the last cell change?
View 2 Replies
View Related
May 16, 2013
My raw data is exported in CSV format. Is it possible for Excel to link to the data or can it only link to an Excel format workbook?
View 1 Replies
View Related
Jan 30, 2007
I have a list of URLs and I wrote a small VBA code to launch them when the user clicks on a button, however, it launches in Internet Explorer. Is there any way to get it to launch in Firefox instead?
View 2 Replies
View Related
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
View Related
Nov 24, 2008
Due to the large number of VLOOKUP functions on my page of data whenever I try to save I get the following error codes. Excel cannot complete this task with available resources. Choose less data or close other applications. Unable to save external link values. The problem itself may not be solvable but is there away to prevent these messages from coming up when I save (or when it autosaves?)?
View 2 Replies
View Related
Oct 26, 2009
We're using SUMIF and it won't work unless the external excel file is open.
This is the formula we're using:
View 7 Replies
View Related
Dec 12, 2006
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!
View 5 Replies
View Related
Feb 1, 2008
I want to be able to get a value from a specific cell in another workbook, the problem i have is that i want to be able to select what file the active workbook looks for from the start page of my current workbook. For example when i load the workbook i get my startup page ( sheet 1) where i will be able to select what external workbook the active workbook will be linking with, i will then link the active workbook to the value in a specific cell from the selected external workbook.
I know to link to another workbook i would use ='[filename]sheet!cell
However i want to be able pull up a 'open file dialog box' which i select a file name which will be used as 'filename' in the formula above.
View 2 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 17, 2014
I'm trying to build a dynamic formula where a cell reference shifts one column over when a certain percentage changes. It's hard to explain, but the attached workbook should make it clear what I'm trying to accomplish.
SampleWorkbook.xlsx
View 2 Replies
View Related
Mar 3, 2009
I need a dynamic formula which if hard-coded would be something like =(tabname!A7).
But instead of A, I want a calculated value such as ($C$2*12+1). My columns are numbered, so I want to say "the 26th column along", "the 37th along" etc, as C changes. I tried the index formula but couldn't get it to work, assuming it is even the right one.
View 5 Replies
View Related
Oct 19, 2009
Q: How can I point excel to the begining value cell if I know the ending value cell and the nubmer of cells to move to get there?
More details:
I have an investing sheet that I need to calculate the CAGR (compound annual growth rate). The formula is (ending value/begining value)^(1/number of years)-1
The number of years can vary but the ending value will aways be in the same cell. I am using a count to see how many years have been entered.
View 3 Replies
View Related
Feb 26, 2007
The original worksheet will have a sheet of data, but I want the user to be able to insert a row of data into this sheet and have the original values still referenced in the macro.
I do not believe the range function will work, because the relative cell positions are what are changing. Also, the offset command would not work, because the positions are not absolute to the active cell. Correct me if I am wrong...
Snippet of
If Shape = "Freeform 30" Then 'AR
Receiver = "kim.randy@companyX.com"
End If
I want it to point to something like =Receivers!C13
How would I do this without hardcoding the data above?
View 9 Replies
View Related
Jul 21, 2006
I have a question about dynamic cell references that I canīt find any info about anywhere else. This is what I want to do: I need to create a sentence that changes depending on user choices. Example:
Cell A1 = Sheet!AX
Displayed text Dear Mr. "name"
Where the X is a number that will change depending on other things. I would love to do something like: =Sheet!A(E1). Where the value in E1 is for example 4. The result would then be =Sheet!A4 and if I changed the value in E1 the reference =Sheet!A(E1) would also change. I donīt know the syntax for doing this or if it is even possible.
View 4 Replies
View Related
