AD Function
=AD(cell_or_range, [abs_num], [a1_r1c1], [include_sheetname], [range_output])
| Argument | Description | Example |
|---|---|---|
| cell_or_range | The reference cell or range to return the address of | A1 |
| [abs_num] | Optional. 1=absolute, 4=relative. Default = 1. | 4 |
| [a1_r1c1] | Optional. 1 for A1 style, 0 for R1C1 style. Default = 1. | 1 |
| [include_sheetname] | Optional. If TRUE, includes sheet name. Default = FALSE. | TRUE |
| [range_output] | Optional. If TRUE, returns full range address. Default = FALSE. | TRUE |
In the template file, navigate to the Addresses worksheet to see the AD function in action.
Description
AD is a utility LAMBDA function that simplifies the use of Excel’s built-in ADDRESS function. It accepts a cell or range and outputs the formatted address for each cell. Optional flags control whether to use relative/absolute format, A1 or R1C1 style, and whether to include the sheet name.
Using default options, AD is basically just a shortcut for the following:
=AD(ref) =ADDRESS(ROW(ref),COLUMN(ref))
=AD(B1:D2, 4) Result: {"B1","C1","D1"; "B2","C2","D2"}
When using the range_output=TRUE option, the function returns the full range as B1:D2 instead of the array {"B1","C1","D1";"B2","C2","D2"}. In this case, the AD function can be more concise. For example:
=AD(B1:D2,,,,TRUE)
=LET(range,B1:D2,arr,ADDRESS(ROW(range),COLUMN(range)),TAKE(arr,1,1)&":"&TAKE(arr,-1,-1))
Result: "$B$1:$D$2"
This function is particularly useful when dynamically generating cell references for documentation purposes, maps, etc.
Lambda Formula
This code for using AD in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.
Code for AFE Workbook Module (Excel Labs Add-in)
/** * Shortcut version of the ADDRESS function. Uses cell_or_range as the input. * * Inputs: * cell_or_range: The reference cell or range for which to return the address(es). * [abs_num]: Optional. Determines the address style (e.g., 1=absolute, 4=relative). Defaults to 1. * [a1_r1c1]: Optional. Determines the reference style (1=A1, 0=R1C1). Defaults to 1. * [include_sheetname]: Optional. If TRUE, includes the sheet name. Defaults to FALSE. * [range_output]: Optional. If TRUE, returns full range (e.g., "A1:C3"). Defaults to FALSE * * Outputs: * Returns the address of the specified cell, optionally including the sheet name. */ AD = LAMBDA(cell_or_range, [abs_num], [a1_r1c1], [include_sheetname], [range_output], LET(doc, "https://www.vertex42.com/lambda/ad.html", version, "1.0.2 - Updated code and added range_output option", cell, cell_or_range, abs_num, IF(ISOMITTED(abs_num), 1, abs_num), a1_r1c1, IF(ISOMITTED(a1_r1c1), 1, a1_r1c1), include_sheetname, IF(ISOMITTED(include_sheetname), FALSE, include_sheetname), range_output, IF(ISOMITTED(range_output), FALSE, range_output), all_addresses, ADDRESS(ROW(cell), COLUMN(cell), abs_num, a1_r1c1), IF(range_output, CONCAT( IF(include_sheetname, "'"&TEXTAFTER(CELL("filename", cell), "]")&"'!", ""), TAKE(all_addresses, 1, 1), ":", TAKE(all_addresses, -1, -1) ), IF(include_sheetname, ADDRESS( ROW(cell), COLUMN(cell), abs_num, a1_r1c1, TEXTAFTER(CELL("filename", cell), "]") ), all_addresses ) ) ));
A1 = "AI Prompt to Copy/Paste" B1 = "Please update my business startup cost spreadsheet for an Excel consultancy business. The first table (Fixed Costs) is in " & AD(B4:D15,4,,1,1) & ", and the second table (Monthly Variable Costs) is in " & AD(B20:D30,4,,1,1) & ". Each table has three columns: Category, Amount, and Notes. Provide realistic sample data for each table."
