Cell Data To Reference External Workbook

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.

ADVERTISEMENT

Link To External Workbook From Cell Reference

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

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 View Related

Reference External Workbook

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

Reference To External Workbook Objects

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

External Workbook - Reference With Single Link?

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

Any Way To Get Countif Function To Reference External Link To Closed Workbook?

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

Assign External Cell Reference To Variable In VBA?

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

Cell Text As Part Of External Reference

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

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:
='[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

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 View Related

VBA Access Common Cell In Many Different Sheets In An External Workbook

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

Add Hyperlink To Shape That Refers To Website Contained In External Workbook Cell

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

Creating External Reference With Variable?

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

External Formula Reference Error

Dec 5, 2012

When I open my Excel workbook, I get the following error:

Removed Feature: External formula reference from /xl/externalLinks/externalLink1.xml part (Cached values from external formula reference)

I am asked if I want to recover the contents of the file.

View 1 Replies View Related

Macro To Remove External Reference Path From Buttons

Jun 11, 2008

I have a little boo-boo I need to fix. I have a customer that has about 100 workbooks. I needed to add some functions to them so I made up a new sheet of forms and buttons as well as a series of new macros to bring into these workbooks. I knew I couldn't import the macros, so I added the module containing those by hand to all workbooks. I then imported a master copy of the new sheet from a master file i use to hold my working macros. The problem is the buttons on the new sheeets in each workbook now references the master workbook for the macro name, even though each workbook has the same macro of the same name in itself.

My question is, can I make a macro that will edit all the buttons on a specific named sheet (maintenance is the sheet name) in a workbook and take out the external reference.

For example, instead of the assigned macro pointing to c:master.xls!macro1 i just want it to call macro1. And yes, I can go into them and do it by hand. Do I want to manually edit 100 workbooks for a dozen buttons each? No. I want to automate it if I can. If anyone can help I would greatly appreciate it. It would save me a bunch of time.

View 9 Replies View Related

How To Break External Reference Link Without Converting Formula To Value Within Sheet

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

Changing Formula To Reference Spreadsheet From External Excel Document

Nov 22, 2011

how to change the formula below which is referencing an internal worksheet, to a forumula that will reference an external worksheet that is located in an excel document on the G: Drive.

=SUMPRODUCT(--(1210laradj_v_2009!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))

So what it's doing is totaling the counts in the 1210laradj worksheet that match columns HMO-ALT SELECT as well as the numbers 3,5,9 etc.

Well now these worksheets will be on a seperate excel spreadsheet located on a network drive, so I am trying to figure out how I can fix this.

I tried adding it in like so, but it fails.

=SUMPRODUCT(--('G:ROC-CLAIMSAudit ResultsProvider infomonthly statsReproc'd claims2011ProductionCounts[0111rpt2235_laradj_v.xls]0111laradj_v_2009'!F2:F65536="HMO-ALT SELECT"),--(ISNUMBER(MATCH(1210laradj_v_2009!AO2:AO65536,{3,5,9,12,37,39,46,47,54,73,74,75,76,77,78},0))))

View 6 Replies View Related

Reference To Cell In Another Workbook

Jul 18, 2014

I have a work book, it copies a cell reference from a work sheet in workbook2 to workbook1

It then selects next work sheet in each page and then triggers the code again.

My problem is i want the following piece of code to work.....what should i put in where it says "activeworksheet"

[Code] ......

View 7 Replies View Related

Cell Reference For Workbook Name

Aug 7, 2009

I am trying to write a formula that will pick the data out of a closed workbook.

Indirect works but it doesnt help because the workbook needs to be open and there are literaly hundreds of workbooks to sort thru.

Is there a way other than find and replace to have a formula pull data from a specific cell in another workbook, based on say cell A1 or A110?

View 9 Replies View Related

Dragging Down Reference To Another Workbook Cell

Jan 23, 2014

I have a master workbook that I reference data from hundreds of excels on.

I am trying to reference the cell "BU7" that has the workbook name (NT-13-001) in it, to reference sheet name "Purchase Order" at cell "H13".

Next line I would like to reference the cell "BU8" that has the workbook name (NT-13-002) in it, to reference sheet name "Purchase Order" at cell "H13".

The computer path is K:FRG TOOL REQ2013NT FormsNT-13-001

This is the formula I have tried... I have also attempted the INDIRECT function & Hyperlink GO TO.

=IFERROR('K:FRG TOOL REQ2013NT Forms["&DU7&".xlsx]Purchase Order'!$H$13,"")

View 1 Replies View Related

Formula With Cell To Reference Other Workbook?

Mar 19, 2014

So finally got my sumrpod working using this formula

=SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm]SPECIALS TEAM NZ'!$C8:$C10000=A5))

Is there a way i can link 'Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[South Pac Load Tracker - MAR14.xlsm] to a cell so that when the next months tracker is created and the filename changes i simply change the filename in the cell which im using to reference the filepath/filename.

e.g A1 = South Pac Load Tracker - MAR14.xlsm

Formula would read =SUMPRODUCT(1*('Q:Documents13-14 FCGP CONTRACTING13-14 TrackingSouthPac - Team Use Only[' & a1 & ;]SPECIALS TEAM NZ'!$C8:$C10000=A5))

View 1 Replies View Related

Reference Workbook And Sheet From A Cell Using VBA

Aug 19, 2009

Is it possible for the VBA code to capture the open workbook name and sheet name from a cell?

I've been trying to tweak this ...

View 6 Replies View Related

VBA To Activate Workbook With Reference To Name In Cell

Jul 9, 2012

i need a VBA code to activate workbook (which is already opened) with reference to name in cell A1

in Cell A1 is "masterworksheet.xls"

View 4 Replies View Related

Reference Worksheet Cell In Workbook

Aug 12, 2006

I have recently learned a little about Excel. Enough to be able to do our payroll for my company on it. My question is: On the 1st page of my workbook, is my quarterly report which shows all taxes, social security, and medicare we hold out each payday for each employee. The rest of the worksheets are for each employee. How do I have each employees withholdings automatically go to my quarterly report page?

View 5 Replies View Related

Reference Cell Of Another Open Workbook

Jun 11, 2008

I would like to know the proper syntax for recalling the value of a cell in another workbook. For example, I want a variable in my code to equal cell(1,1) in sheet1 of workbook1.xls.

View 5 Replies View Related

Reference Cell From Another Workbook With Part Of Location?

Feb 20, 2014

I have the following formula/array:

='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4

Now this works perfectly fine, but I am wondering if it is possible to replace 2013-004 so that it reads the text from a cell on that sheet to complete the file name.

Example:

A
B

1
2013-004
='fake folder location[2013-004.xlsx]MgmtRpt'!$A$4

I want to replace the 2013-004 in B1 with text from A1. I have tried multiple methods but it just always assumes it is part of the file name or breaks the formula.

View 2 Replies View Related

Absolute Cell Reference In Hyperlink Within Same Workbook?

Jul 12, 2013

I have a workbook with many tabs and within those tabs there are many hyperlinks to other areas within the same workbook. When I add or delete lines or rows, the corresponding hyperlinks do not stay with the changed cell. Is there any way to make the cell reference in a hyperlink an absolute similar to how you do with a formula?

View 2 Replies View Related

Activate Open Workbook From Cell Reference

Aug 20, 2008

activate an open workbook based on the workbook name that is typed into a cell that is a named range?

So, I keep wanting to do this:
Windows.Activate Filename:=Range("MyRange")

Because this works:
Workbooks.Open Filename:=Range("MyRange")

View 9 Replies View Related

Reference Data Validation Cell From Another Workbook

Sep 17, 2008

I'm trying to link a range of cells in one workbook to a range in another workbook, both in a network drive. Everything is working out well except for the cells that have a data validation list on them. the formula linking one cell is ='G:Destination To[Workbook2.xls]SheetX!B13 so I'm just trying to get the value in the cell. I don't need the validation list to transfer just the value of what was selected. If I go to that cell and type something in then it transfers over just fine. If the validation list was used in Workbook2 then I get a #N/A in Workbook1 that is linking to the Workbook2 cell.

How can I link the cell with a data validation list in it to a cell in another workbook so that I can get the data in the cell (whatever was selected from the validation list)?......

View 4 Replies View Related

Add COUNTIF To Cell & Reference Closed Workbook

Jul 27, 2007

i'm trying to do a COUNTIF from an open workbook. The range I want for it is in another workbook, which I do not want to have open every time I run the macro.

This is my current
Sub cellLink()
Sheets("Calls In-Out Trend").Range("ag18").Formula = _
"=COUNTIF('C:***[*.xls]sheet name'I:I, QXO)"
End Sub

editthe range is just so I can see if it's giving the right values, the range won't be like that once I get it working. I'll probably be using logic statements to place them in the appropriate cells. The * is just me taking out the directory names, its on the desktop)

It can copy cell values from another unopened workbook, but when I try to put in COUNTIF it doesn't want to compile/run the script.

That's basically the question. Here's some background and my aim:

I'm scanning row I:I for certain keywords which I then count up and put into corresponding columns of a chart.

View 9 Replies View Related

Formula Reference To WorkBook With File Name In Cell

Apr 9, 2008

I need to reference a cell to another cell in a different workbook (not opened), however the name of this workbook contains a variable in its name. Something like:

='C:[Week" & variable &".xls]R23'!$D$3

where variable is the number of the week. I have tried using a cell name instead of a variable: ='C:[Week=($B$3).xls]R23'!$D$3

and I get a dialog box to update values by selecting a file.

View 4 Replies View Related

How To Reference A Cell In Another Workbook And Return Cell Value And Comment

Nov 2, 2012

What is the best way to reference a cell in another workbook and return the cell contents and the comment on that cell. I would like the comment to come across as a comment in the new workbook becuase the comment is actually a picture.

Hope this makes sense. I did find a macro through googling but I couldn't get it to work? I don't really want to copy and paste because eventually I have hundreds of sheets & thousands of cells to refer to.?

View 2 Replies View Related

Cannot Find Link To External Workbook

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

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

INDIRECT Update With External Workbook

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

Copy Module To External Workbook

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

Editing External Workbook With Code

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

How To Reference Other Sheet Names In Workbook But Keep Cell References The Same

Oct 7, 2013

Within one workbook I have 15 sheets, 13 are for separate divisions within the company, 2 are used to present sums across the 13 sheets. All 13 sheets have identical columns and rows, with unique numbers in each cell. Right now I am manually selecting the cells to reference in my master sheet, =sheet name!cell+sheetname!cell+sheetname!cell - so and and so on. How can I reference the sheet name once, and then all of the cells to SUM? =sheetname!(c4+c14+c24+c34) etc?

This would let me be able to copy the formula from cell to cell, and only have to change the sheet name each time.

View 3 Replies View Related

Save File / Workbook As Cell Reference Filepath & Name

Feb 8, 2008

I'm trying to get Mac OS X Excel VBA to understand this code however the ChDir reference and the filepath seem to be causing problems. I would like to specify in the code where the file should be stored but I did try removing the filepath section altogether but to no avail.

Option Explicit
Sub SvMe()
'Saves filename as value of A1 plus the current date
Dim newFile As String, fName As String
' Don't use "/" in date, invalid syntax
fName = Range("A1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
newFile = fName & " " & Format$(Date, "mm-dd-yyyy")
' Change directory to suit your PC, including USER NAME
ChDir _
"C:Documents and Settings USER NAME Desktop"
ActiveWorkbook. SaveAs Filename:=newFile
End Sub

View 2 Replies View Related

Reference Part Workbook Name In Formula From Date In Cell

Feb 20, 2008

i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.

so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.

any ideas would be appreciated. know how to do this in VBA but no clue with formulas.

what i am trying to do is concatenate part of a file name and a cell value which contans a date

=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)

View 3 Replies View Related

ADVERTISEMENT