Retrieving Values other Files using Address & Indirect Functions.

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