ABSREF

Macro Sheets Only

Returns the absolute reference of the cells that are offset from a reference by a specified amount. You should generally use OFFSET instead of ABSREF. This function is provided for users who prefer to supply a absolute reference in text form.

Syntax

ABSREF(ref_text, reference)

Ref_text specifies a position relative to reference. Think of ref_text as "directions" from one range of cells to another.

• Ref_text must be an R1C1-style relative reference in the form of text, such as "R[1]C[1]".
• Ref_text is considered relative to the cell in the upper-left corner of reference.

Reference is a cell or range of cells specifying a starting point that ref_text uses to locate another range of cells. Reference can be an external reference.

Remarks

• If you use ABSREF in a function or operation, you will usually get the values contained in the reference instead of the reference itself because the reference is automatically converted to the contents of the reference.
• If you use ABSREF in a function that requires a reference argument, then Microsoft Excel does not convert the reference to a value.
• If you want to work with the actual reference, use the REFTEXT function to convert the active-cell reference to text, which you can then store or manipulate (or convert back to a reference with TEXTREF). See the third example following.

Examples

ABSREF("R[-2]C[-2]", C3) equals $A$1

ABSREF(RELREF(A1, C3), D4) equals $B$2

REFTEXT(ABSREF("R[-2]C[-2]:R[2]C[2]", C3:G7), TRUE) is equivalent to

REFTEXT(ABSREF("R[-2]C[-2]:R[2]C[2]", C3), TRUE), which equals "$A$1:$E$5"

In Microsoft Excel for Windows ABSREF("R[-2]C[-2]", [FINANCE.XLS]Sheet1!C3) equals [FINANCE.XLS]Sheet1!$A$1.

In Microsoft Excel for the Macintosh ABSREF("R[-2]C[-2]", [FINANCE]Sheet1!C3) equals [FINANCE]Sheet1!$A$1

Related Functions

RELREF Returns a relative reference

List of Lookup & Reference Functions