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
1/1/10
IF Function in Excel
=IF(logical test, value if true, [value if false])
The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.
The IF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
» logical_test Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
» value_if_true Required. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument.
» value_if_false Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE. If logical_test evaluates to FALSE and the value of the value_if_false argument is omitted (that is, in the IF function, there is no comma following the value_if_true argument), the IF function returns the value 0 (zero).
The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. For example, the formula =IF(A1>10,"Over 10","10 or less") returns "Over 10" if A1 is greater than 10, and "10 or less" if A1 is less than or equal to 10.
The IF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
» logical_test Required. Any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. This argument can use any comparison calculation operator.
» value_if_true Required. The value that you want to be returned if the logical_test argument evaluates to TRUE. For example, if the value of this argument is the text string "Within budget" and the logical_test argument evaluates to TRUE, the IF function returns the text "Within budget." If logical_test evaluates to TRUE and the value_if_true argument is omitted (that is, there is only a comma following the logical_test argument), the IF function returns 0 (zero). To display the word TRUE, use the logical value TRUE for the value_if_true argument.
» value_if_false Optional. The value that you want to be returned if the logical_test argument evaluates to FALSE. For example, if the value of this argument is the text string "Over budget" and the logical_test argument evaluates to FALSE, the IF function returns the text "Over budget." If logical_test evaluates to FALSE and the value_if_false argument is omitted, (that is, there is no comma following the value_if_true argument), the IF function returns the logical value FALSE. If logical_test evaluates to FALSE and the value of the value_if_false argument is omitted (that is, in the IF function, there is no comma following the value_if_true argument), the IF function returns the value 0 (zero).
Subscribe to:
Posts (Atom)