Indirect Reference To A Separate Workbook
Jun 26, 2014
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
=INDIRECT("'"&A1&"'!A1:AJ1500")
but that just returns the contents of A1 in the target sheet.
View 1 Replies
ADVERTISEMENT
Jan 17, 2008
I am writing a formula in cell "B1" in "December 2007 REVISED BY DAN.xlsm" that will reference cell "X1" in a workbook that has not been created yet.
The way I will tell the formula what the name of the workbook will be is by referencing cell "A1" and adding the extension ".xlsx" to it.
Example: ...
View 14 Replies
View Related
Jan 31, 2014
I usually use INDIRECT to link to a separate spreadsheet. Is there a way i can use this without opening the separate spreadsheet
This is a sample of the formula =VLOOKUP($F$14,INDIRECT('Data Files'!$C$17 & "$A:$Z"),5,0)
View 1 Replies
View Related
Feb 17, 2012
Reduced to it's simplest form I have a formula
=1*(FY2012_TY), where FY2012_TY
is a Defined Name which refers to a column on another tab in the workbook, and is defined as:
=OFFSET(Table!$AA$3,0,0,COUNT(Table!$AA:$AA),1)
What I would like to do is to come up with an approach to this formula that would allow me to drag it across columns and have the year automatically increment (e.g., FY2013_TY, FY2014_TY, all of which are similarly defined Defined Names referring to adjacent columns). I have tried various approaches using INDIRECT along with CONCATENATE, but when it comes down to evaluating the formula, I consistently get a #REF! error.
View 4 Replies
View Related
Feb 1, 2009
I have an income statement with the cities on top (column header) and the expenses below it. There are 5 cities for example. The last line is net profit before it changes to the next city.
New York (column header)
Wages
Lights
Net Profit
Boston
Lights
Wages
Sales
Net Profit
How do you get the row reference for Boston Net Profit without using the offset or indirect function? (doing external linking with workbook closed) The formula would find Boston first and then look for the first net profit after Boston? The small if function may work for this.
View 9 Replies
View Related
Feb 13, 2014
Is there anyway to add text to an INDIRECT reference so that it will now reference a new range?
For instance, this is my formula...
=IF(INDEX(INDIRECT("A1+TM"),MATCH(B2,INDIRECT(A1),0),1)="","-",INDEX(INDIRECT("A1+TM"),MATCH(B2,INDIRECT(A1),0),1))
I am trying to say, if the cell 1 column to the left of the cell that matches the text in B2, in the cell range referenced in A1 (CWS) "added to the text "TM", is blank, then write "-", otherwise return the number in the cell 1 column to the left of the cell that matches the text in B2, in the cell range referenced in A1 (CWS)+"TM".
Cell A1 is a data validation list that refers to different ranges. The only difference between those named ranges and the named range I want to reference is the text "TM" comes at the end.
View 3 Replies
View Related
Feb 7, 2012
I have the following formula which i'm using to indirectly reference a worksheet. The worksheet i'm referencing includes named ranges that i'd like to call on but i can't get the syntax right.
Code:
=SUMPRODUCT(--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")>=D5),--(INDIRECT("'"&HH_Elec_Title&"'!B15:B19")
View 6 Replies
View Related
Oct 15, 2013
I am trying to use the sumif and indirect function together but I keep getting an error and I'm not sure why.
Actual formula:
=SUMIF('SWG OR'!B:B,A168,'SWG OR'!E:E)
Using the sumif/indirect function, I have the following:
> Cell B5 contains the name of the tab "SWG OR"
=sumif(indirect(B5&"!B:B",A168,B5&"!E:E"))
View 2 Replies
View Related
Mar 12, 2008
I'm having difficulty referencing named ranges indirectly in a VLOOKUP formula (Excel 2003).
Cell A1 contains a fuel cost.
Cell A2 contains a formula that returns a RangeName.
In Cell A3, I want to enter a VLOOKUP formula that uses the range returned as the result in Cell A2 as the Table_array. In other words: =VLOOKUP($A$1,$A$2,2,FALSE). The result is #NA!.
If I use =VLOOKUP($A$1,INDIRECT($A$2),2,FALSE), I get #REF!.
Among other things, I've also tried =VLOOKUP($A$1,TEXT($A$2,"@"),2,FALSE). No cigar.
View 9 Replies
View Related
Feb 22, 2010
I have a worksheet with a name that inckudes a hyphen, e.g. 1234-5678.
And I have the worksheet name in a cell, say, I!.
Using
=INDIRECT(I$1&"!YADDA")
I get a REF error because of the inclusion of the hyphen in the worksheet name.
How can I fix this without eliminating the hyphen?
View 9 Replies
View Related
Oct 2, 2008
I'm trying to combine text and a cell reference using Indirect. Cell B1 contains the text "Region" (no quotation marks). In another cell I'm trying to create a reference to "Region total" (no quotation marks). By my limited understanding the below should work: =INDIRECT("b1"&" total")
But the cell returns #REF!. I've tried pretty much every combination of quotation marks around different elements of the formula I can think of. Cell B1 is a drop down list, don't imagine that's relevant, but it may be.
View 2 Replies
View Related
Jun 9, 2009
I have this formula in excel 2007:
=COUNTIFS(Blad1!W:W;"1";Blad1!X:X;"H")+COUNTIFS(Blad1!W:W;"6";Blad1!X:X;"H").
Blad is Dutch for sheet, by the way.
In this formula, I want to let the ranges in Sheet1 be dependent of values in Sheet2. The formula itself is in Sheet2.
W must be replaced by the value of Sheet2!B12 and X to be replaced by the value of Sheet2!B9.
I tried this by using the INDIRECT formula, but the quotation marks of the search values are giving errors.
View 6 Replies
View Related
Oct 11, 2011
I need making a dynamic cell reference in an indirect formula, which looks at a different workbook.
The other workbook is called: ESF.xlsx
The sheet name in the other workbook is defined in Cell B2
The first cell i need to pull across is B115.
In cell B8, This is what i have so far:
=INDIRECT("[ESF.xlsx]"&B2&"!"&"B115")
How do i make it so that i can copy this formula quickly, but so only the row and column reference change?
Like in C8 i would want it to look at cell C115, In C9, id want it to look at C116 etc.
View 2 Replies
View Related
Dec 9, 2012
I have been using the following formula to do conditional summing over multiple worksheets:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$AB$2:$AB$13&"'!b3:b39"),$B16,INDIRECT("'"&$AB$2:$AB$13&"'!c3:c39")))
The formula works fine, but when I drag it to the next column c3:c39 stays the same. How do I get that reference to change when I drag the formula over multiple columns.
View 1 Replies
View Related
Apr 30, 2009
How would I used the indirect function to reference a named range in the workbook in formula creation.
For instance, if in cell A1 I have entered the text "Sales" which is also a named range in the workbook. Then in cell A2 I tried entering the following formula to sum based on the entry in cell A1 using the indirect funtion. For instance:
=sum(indirect(A1))
In this instance I was hoping this formula would then sum the amounts in the "Sales" named range. And, if I changed the text in cell A1 to "Cost" for instance (another named range in the workbook), it would sum the amounts in the "Cost" named range. Allowing for a dynamic formula based on the entry in cell A1.
I'm using Excel 2007.
View 9 Replies
View Related
Sep 28, 2006
I want to create a formula of the form: =INDIRECT(ADDRESS(4,2,2,TRUE,($A9) and insert this in a cell BUT the $A9 reference needs to reference the row of the cell where the formula is being inserted which will vary. ie. A30, A31, etc. How do I do this?
View 2 Replies
View Related
Dec 3, 2007
COPYING indirect formula. When I copy, the lookup reference does not change. My formula is =INDIRECT("'"&$A247&"'!j106"). When I copy horizontally across cells, I want J106 to increase, ie j107, j108 etc. At the moment it remains at J106 and i have to manually increase the numbers by 1.
How do I change my formula so that the numbers increase automatically?
View 6 Replies
View Related
Feb 25, 2013
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
View 4 Replies
View Related
Apr 4, 2014
Basically I have an equation:
=SUMPRODUCT(G9:G11,H9:H11)/SUM(G9:G11)
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
"A1"=SUMPRODUCT(INDIRECT("G9:G"&MIN(ROW(G9)+A8-1)),INDIRECT("H9:H"&MIN(ROW(H9)+A8-1)))
Where the user inputs the number of rows used in A8.
The solution for the lower part of the fraction is this:
"A2"=SUM(INDIRECT("G9:G"&ROW(G9)+A8-1))
[code]....
View 3 Replies
View Related
Jul 10, 2014
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:
=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!G:G")))
The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.
View 6 Replies
View Related
Jan 24, 2012
Trying to use INDIRECT to sum the contents of a column on another worksheet upto a certain cell reference which is in another cell on the worksheet.
=SUM(INDIRECT("Sheet1!A4:Sheet1!"&B1))
I have taken it back to simply having sheet1 with numbers in A4 to A23, then sheet2 with A23 in cell B1, and the formula above it C9, but I keep getting #REF!.
The formula works fine if on sheet1 without the worksheet names in it. Formula evaluation gets to =SUM(INDIRECT("Sheet1!A4:Sheet1!A23")) then gives =SUM(#REF!)
View 6 Replies
View Related
May 21, 2014
Attached I have a document where I am wanting all of the individual sheet names on the Total page. Rather than having to change each formula to match the sheet name I believe there is a way to reference the sheet name column (AO) in the formula so you can drag it down to fill in the columns. I am basic with Excel and do not know how to add this indirect function into my current formulas.
View 5 Replies
View Related
Apr 23, 2014
I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.
View 9 Replies
View Related
Feb 11, 2010
I m not able to use the standard Excel Paste Special function when I copied a range of cells in another Excel workbook opened in a separate instance of Excel. Instead, Paste Special thinks that I have copied some non-Excel objects and gave me the Paste As options. This is not the case if I open both workbook within the same instance of Excel. Could you share with us if there is a trick to trigger the normal Paste Special options in such situations (without having to invoke Macro procedures)?
View 2 Replies
View Related
Aug 31, 2013
I need to create a reference such as TUT001 from Tutorial in a separate cell.
Also where there are 2 names that have the same 3 letters at the start i need the number to be 002.
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
Feb 21, 2014
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
View 4 Replies
View Related
Jul 16, 2012
I have been tasked with creating a macro which creates a new workbook wherein each sheet contains the information for one site from the active sheet. The active sheet already has the values sorted by the site such that all information needing to be copied from the active sheet into the new workbook is together.
I.E.
ATL
ATL
ATL
ATL
CEN
CEN
JCK
JCK
etc.
There are 8 different sites on the active sheet: ATL, CEN, DAL, HAR, JAS, JCK, VIS, NOV
The macro needs to find the range for all of the data of each site and copy/paste that data into a new workbook such that ATL would have its own sheet, CEN would have its own sheet, and so on. The data ranges from A:R.
So, for example, the macro would find that the last row with ATL in the "B" column is 6095 and would then copy A2:R6095 and insert that data into the new workbook under Sheet 1.
I had some code that I had adapted to select the range for each of them, but the code loops through the entire sheet (which is 44,307 rows long) for each site making it a quite clunky and very slow step in an even longer macro. Since the data is already sorted, I know there must be a way to have the macro stop searching when it reaches data not equal to the data the row before, however, my experience with VBA is limited, and I have been unable to find a solution. Also, the data does not have to be conserved after being sent to the new workbook, if that would speed up the macro.
View 5 Replies
View Related
Apr 30, 2012
I have a file that summarizes data by country and by month but then another file with a yearly summary. I need to pull in the same cell references for each month but using each tab's name: 'Jan' tab, 'Feb' tab etc. The problem is every time I tell it to find/replace the tab name, I have to select the file from its location in a browsing box every time. I have also tried the indirect formula without much luck and because I also had to add all the month summary tabs to the original file to get the indirect formula to work. I would rather keep both files separate.
How do I copy over formulas in the Jan column and change the tab reference to the Feb tab for the Feb column without having to change things 200 times for each countries data?
View 2 Replies
View Related
Nov 2, 2007
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.
View 9 Replies
View Related
