The Google Sheets function ADDRESS is a powerful tool that allows users to generate a cell reference in string format. This function is particularly useful when you need to create dynamic references based on variable row and column numbers, making it easier to work with data in various scenarios.

Syntax

ADDRESS(row, column, [abs_num], [a1], [sheet])

• row: The row number of the cell reference you want to return.
• column: The column number of the cell reference you wish to return.
• abs_num: (Optional) A number that specifies the type of cell reference: 1 for absolute (e.g., $A$1), 2 for absolute row and relative column (e.g., A$1), 3 for relative row and absolute column (e.g., $A1), and 4 for relative (e.g., A1).
• a1: (Optional) A boolean value that specifies the reference style: TRUE for A1 notation and FALSE for R1C1 notation.
• sheet: (Optional) A string that specifies the name of the sheet to use for the reference.

Example #1

ADDRESS(3, 2)

Using the function with row 3 and column 2 will return “B3” as the cell reference since it corresponds to the second column (B) in the third row. Result: B3

Example #2

ADDRESS(1, 1, 1)

This call specifies an absolute reference for the first row and first column, resulting in “$A$1”. Result: $A$1

Example #3

ADDRESS(5, 3, 4, FALSE, "Sheet2")

In this example, the function returns a relative reference from “Sheet2” for row 5 and column 3, which will be “C5”. Result: C5

Error handling

• REF!: This error indicates that the specified row or column number is invalid, usually because it exceeds the number of available rows or columns in the sheet.
• VALUE!: This error occurs when the input arguments are not numbers; for instance, providing text where row or column numbers are expected.
• N/A: This implies that the specified worksheet does not exist if a sheet name is provided.

Conclusion

In summary, the ADDRESS function in Google Sheets is an invaluable asset for users looking to create dynamic cell references effectively. By understanding its parameters and potential errors, users can leverage this function to enhance their spreadsheet capabilities significantly.