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


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

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


View 3 Replies View Related

VBA - Add Formula To Reference Another Workbook?

Jun 20, 2013

I believe this is the last piece of the puzzle of a project I'm working on. Here are the components I have to work with:

Component 1:

Dim file As Range
Set file = Range("rgFileToBeChecked")
'this sets "file" as the full path


View 9 Replies View Related

Reference Another Workbook In Formula

Nov 11, 2009

I am having trouble inserting a cell reference into a formula. I have a spreadseet that uses data from another workbook. The workbook name changes monthly, so i'd like to be able to place the workbook name in a cell and have the formulas in the spreadsheet use that cell to get the name of the workbook to get the data from. So:

if, for eg, the formula in my spreadsheet is ='[test data file.xlsx]Sheet1'!$B$4+'[test data file.xlsx]Sheet1'!$B$6

I would like to be able to enter test dta file.xlsx into cell A2 of my spreadsheet and replace a reference to A2 into the formulas. I've tried several ways, but can't seem to get it to work.

View 3 Replies View Related

Reference Another Workbook In A Formula

May 15, 2007

I have a formula that calculates data from the workbook that the formula is in. I need it to do the same calculations only pull the data from another workbook. Here is a sample formula:

= SUM(X1504 + SUMPRODUCT((H1:H394=73)*(U1:U394="NVLIV"),S1:S394))

Would I do something to the nature of:

=SUM(C:/sample.xls[Sheet1$x1504] + SUMPROD...etc...?

Basically how do you reference another workbook that is closed?

View 4 Replies View Related

Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference

Aug 14, 2006

I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

View 3 Replies View Related

Formula To Reference Another Workbook Using Cells

Nov 19, 2003

I'm trying to do: In Book2.xls, Sheet1, Column A, I have a list of tab names in another workbook (2003 Com October.xls). In column B of Sheet1, I want to utilize what's in column A to pull data from the correct tab in 2003 Com October.xls. Here's what the result should resemble:

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2B3B4B5B6B7=
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

View 9 Replies View Related

Change Formula Cell Reference Based On Another Cell Reference

Jan 8, 2012

How do I change a formula cell reference based on another cell's reference? I'm building a schedule that looks to a task's trigger and adds days based on that relationship. All entries in column "A" will be text and all cells in "B" will be the simple formula "=A2" or "=A3". Due date is calculated by adding the value in "C" to the preceding date in column "D". In the spreadsheet below, the trigger for "Budget set" is "Specs written" with 3 days added to the previous due date.

1 Task___________Trigger_____________Days_____Due Date
2 Design begins__Proj OK______________10____10-Jan
3 Specs written__Design begins (A2)____5____15-Jan (D2+C3)
4 Budget set_____Specs written (A3)____3____18-Jan (D3+C4)

If the trigger for A4, "Budget set", changed from A3 to A2, is there a way that the formula that determines the due date in D4 could read the trigger cell reference in B4 so that the value in the corresponding row in column "C" is added in the date column?

View 4 Replies View Related

How To Replace Workbook Reference Filename On A Formula

Apr 6, 2014

I have to many refrence links I need a macro that replace the filename. How to replace workbook refrence filename on a formula using cell value,


from [13.xlm]2014.Goals'!C8 to ["cell-value".xlm]2014.Goals'!C8

View 1 Replies View Related

Copy Formula From 1 Workbook To Another With Reference Changing

Apr 17, 2008

I want to copy a formula from Workbook A to Workbook B and have the formula configured with Workbook B's spreadsheet cells, not Workbook A's.

View 2 Replies View Related

Cell Reference :: Formula To Reference New Cells

Feb 15, 2010

I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.


12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View 14 Replies View Related

Excel 2007 :: Not Being Allowed To Input Formula With Reference To Another Workbook

Feb 13, 2012

I am having trouble making any formula references between 2 different workbooks.

For example, I being typing out my VLOOKUP function on 1 sheet, as soon as I get the table argument, I switch over to the other workbook in which the table is, and I no longer have the VLOOPKUP function active.

I am having this problem for any formula in which I am trying to reference this workbook.

I have ensured they are both saved as Excel workbook documents. I am using Excel 2007

View 3 Replies View Related

Formula Copy & Paste: Reference Other Worksheets Within The Workbook, Are Copied Over As Links

Apr 28, 2007

I'm trying to perform a copy & paste operation by hand which to me should be easy in theory. I have a worksheet whose formulas have been corrupted. I have a backup of this sheet- I'd like to copy the backup sheet into the workbook. The problem is that the formulas, which reference other worksheets within the workbook, are copied over as links to the backup workbook instead of the new workbook they are being copied too. This seems simple, but how do I copy the worksheet and the references without having to go through all of the references by hand to only apply to the local book.

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

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

Excel 2007 :: Formula To Return First Cell Reference In Another Cell Formula

Oct 1, 2011

Version: Excel 2007 WinXP

I'm basically looking for something almost like an inverse function to INDIRECT. This function would first look at a cell's formula as a text string, parse out the first valid cell reference in A1 format, and return that cell as a text string.

Detail: I have a spreadsheet with cells that point to other values. I would like to get only the row number from the first cell reference in the formula residing in a given cell. For example:

Suppose A1 has the formula =AL267. and A2 has the formula =SUM(AL94:AL235)

I would like a formula in B1 that returns the text string, "AL267" so that I would know this is the first reference.

Ideally it could be dragged down to B2 such that it returns the text string "AL94" (and not "AL235") because AL94 is the first cell reference in A2's

Currently I am copying the formulas after hitting ctl+` and pasting that text into a text editor, followed by text operations to manipulate the results into the desired values. Any solution that didn't involve going out to notepad.

View 2 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.



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

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

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

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.

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

Copyrights 2005-15, All rights reserved