Quote:
Originally Posted by Northendzone
Here is an example of an indirect I set up for something I was working on:
SUM(INDIRECT("'"&$AM$3&"'!Y"&AL6&":Y"&AM6))
AM3 is the filetab name in my workbook
AL6 = 3
AM6 = 6
So this formula adds up the values in Y3:Y6 on the file tab entitled "Whatever"
In my case the file tab Whatever was in the same workbook - you will have to do a little extra work because your are referencing other workbooks.
it is hard to wrap your head around - but once you get it it could change your life
|
While INDIRECT can reference an external excel spreadsheet, that external excel spreadsheet must be open.
Here's a working example I just did.
values.xlsx (the top spreadsheet) just has values 5, 10, and 20 in the top row.
test2.xlsx has the file name (A1), Sheet name (B1), Cell name (C1), a formula that creates a string that refers to values.xlsx (D1), and then the evaluation of that string using indirect (E1).
The formula for D1 is displayed - it just concatenates the file, sheet, and cell, and adds the square brackets and ! where needed.
The formula for E1 is simply: =INDIRECT(D1), and you can see it displays the proper value, 20.
However, if values.xlsx is closed, then you will get a #REF error.
I think the only way around this is to use a macro to open each file and retrieve the value for you.