Retrieving Values other Files using Address & Indirect Functions.
First of all you must understand the Address and Indirect Functions
Address Function: ADDRESS function is used to obtain the address of a cell in a worksheet, given specified row and column numbers. For example, ADDRESS(1,1) returns $A$1 and ADDRESS(10,2) returns $B$10.
Syntax: =ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
row_num: Required. A numeric value that specifies the row number to use in the cell reference.
column_num: Required. A numeric value that specifies the column number to use in the cell reference.
abs_num: Optional. A numeric value that specifies the type of reference to return.
1 or omitted Absolute (the exact address of a cell, regardless of the position of the cell that contains the formula. Example $A$1.)
2 Absolute row & relative column (A$1)
3 Relative row; absolute column ($A1)
4 Relative (A1)
Sheet_Text: Optional Name of the Sheet that is required in Cell Address
Indirect Function: Retrieves the Value of Any range/Cell Specified by the Text String.
Syntax: INDIRECT(ref_text, [a1])
ref_text: Text String that is written a Address of any Cell or range, Like "A1", "$A$1", "Sheet1!$a$1"
a1: Optional logical value that specifies what type of reference is contained in the cell ref_text.
a1 = FALSE: ref_text is as an R1C1-style reference.
a1 = TRUE: ref_text is as an A1-style reference.
Let’s come back to our Objective.
To this purpose we need to have 2 more Parameters that are File Path & File Name
Generally in any Formula we Refer to any cell in
c:\Folder Name\[File Name.ext]SheetName!RowColumn
So Our Formula will be
=Indirect(“File_Path” &”\” & “File Name.ext” & Address(Row,Column,1,1,”Sheet1)
For Example we we want to Retrive some value from any Excel File that is stored on C:
=Indirect(“C:\My File.xlsx”,address(1,1,1,1,”Sheet1”))
This will retrieve the value from Cell A1 of C:\My File.xlsx
