Products

Documentation keyboard_arrow_right Formulas keyboard_arrow_right Functions keyboard_arrow_right Address

ADDRESS function

PRO

The ADDRESS function in Jspreadsheet Formulas Pro is a powerful reference manipulation tool that converts numeric row and column coordinates into cell references. This versatile function is essential for:

  • Dynamic spreadsheet automation
  • Formula generation and manipulation
  • Cross-sheet referencing
  • Data validation and lookup systems
  • Macro development
  • Custom function creation

Key capabilities include:

  • Converting numeric positions to cell references
  • Supporting both A1 and R1C1 notation styles
  • Managing absolute and relative references
  • Enabling cross-sheet references
  • Handling dynamic range creation
  • Supporting programmatic formula building

The function is particularly valuable for:

  • Spreadsheet developers creating custom solutions
  • Financial analysts building complex models
  • Data analysts automating workflows
  • Business users creating dynamic reports
  • System integrators developing spreadsheet tools
  • Quality assurance teams automating tests

Unlike basic cell references, ADDRESS provides programmatic control over reference creation, making it indispensable for advanced spreadsheet development and automation.

Documentation

Returns a cell reference as a string, given row and column numbers, and an optional flag indicating whether the reference should be absolute or relative.

Category

Lookup and reference

Syntax

ADDRESS(row_num, col_num, [abs_num], [a1], [sheet_text])

Parameter Description
row_num The row number for the cell reference.
col_num The column number for the cell reference.
[abs_num] Optional. A number specifying the type of reference to return. May be one of: 1 (default), 2, 3, or 4.
[a1] Optional. A value indicating whether the returned reference should use the A1-style or R1C1-style notation. Defaults to FALSE if not specified.
[sheet_text] Optional. The name of the worksheet to which the cell reference belongs.

Behavior

The ADDRESS function generates a cell address in text format based on specified row and column numbers. It takes the row and column numbers as primary arguments, and three optional arguments: abs_num, a1, and sheet_text.

  • If the column number is omitted or is less than 1, the function will return a #VALUE! error.
  • For the abs_num argument, the function accepts values from 1 to 4. If the argument is omitted or not in this range, the function will default to an absolute reference (1).
  • If the a1 argument is FALSE, the function will return an R1C1-style reference. If it's TRUE or omitted, the function will return an A1-style reference.
  • For the sheet_text argument, the function will return an address that includes the sheet name if a valid string is provided. If the argument is omitted, the function will return an address in the active sheet.
  • If the function is given a boolean value, text, or an error value as a row or column number, it will return a #VALUE! error.

Common Errors

Error Description
#VALUE! This error is returned when the row or column number is less than 1, non-numeric, a boolean, text, or an error value.

Best practices

  • Always ensure that the row and column numbers provided are valid (i.e., greater than zero and numeric).
  • Make use of the abs_num argument to control the type of cell reference you want to generate.
  • If you're working with multiple sheets, you can use the sheet_text argument to generate addresses in different sheets.
  • Remember that the ADDRESS function returns a text, not an actual reference. If you want to use the result as a reference, you should combine it with INDIRECT function.

Usage Examples

Here are comprehensive examples demonstrating the ADDRESS function's capabilities:

1. Basic Reference Creation:

// Absolute reference (default)
=ADDRESS(1, 1)      // Returns "$A$1"
// Fully relative reference
=ADDRESS(2, 3, 4)   // Returns "C2"
// Mixed reference (absolute row)
=ADDRESS(4, 4, 2)   // Returns "D$4"

2. Dynamic Range Generation:

// Create dynamic cell references
=ADDRESS(ROW(), COLUMN())   // Current cell address
=ADDRESS(ROW()+1, COLUMN()) // Cell below current
=ADDRESS(ROW(), COLUMN()+1) // Cell to the right

3. Cross-sheet References:

// Reference with sheet name
=ADDRESS(5, 3, 1, TRUE, "Sheet2")  // Returns "Sheet2!$C$5"
// Multi-sheet reference
=ADDRESS(1, 1, 1, TRUE, "'Data Sheet'")  // Returns "'Data Sheet'!$A$1"

4. Advanced Applications:

  1. Dynamic Lookup Tables:
// Create lookup reference
=ADDRESS(MATCH(value, range, 0), 2)  // Dynamic row lookup
=ADDRESS(2, MATCH(header, headers, 0))  // Dynamic column lookup
  1. Range Building:
// Dynamic range start
=ADDRESS(1, 1) & ":" & ADDRESS(ROW(), COLUMN())
// Result: "$A$1:current_cell"
  1. Formula Construction:
// Build SUM formula
="=SUM(" & ADDRESS(1,1) & ":" & ADDRESS(10,1) & ")"
// Result: "=SUM($A$1:$A$10)"

5. Real-world Scenarios:

  1. Financial Reporting:
// Dynamic total reference
=ADDRESS(last_row, summary_col, 1)  // Bottom-line totals
// Year-over-year comparison
=ADDRESS(curr_row, curr_col-12)     // Previous year reference
  1. Data Validation:
// Reference validation range
=ADDRESS(2,validation_col) & ":" & 
ADDRESS(last_row,validation_col)    // Create validation range
  1. Dashboard Creation:
// Dynamic chart ranges
=ADDRESS(data_start, 1) & ":" & 
ADDRESS(data_end, data_cols)        // Chart data range
  1. Automated Reporting:
// Report cell references
=ADDRESS(report_row, summary_col, 2) // Mixed reference for copying
=ADDRESS(fixed_row, curr_col, 3)     // Mixed reference pattern

6. Special Applications:

  1. Error Handling:
// Safe reference creation
=IF(row_num>0, ADDRESS(row_num,col_num), "#N/A")
// Validates inputs before creating reference
  1. Multi-sheet Operations:
// Cross-sheet calculations
=ADDRESS(source_row, source_col, 1, TRUE, "Data") &
"=" & ADDRESS(calc_row, calc_col, 1, TRUE, "Calcs")
  1. Dynamic Forms:
// Form field references
=ADDRESS(base_row + field_offset, input_col)
// Creates references for form fields

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Row",
        "Column",
        "Address Result"
    ],
    [
        2,
        3,
        "=ADDRESS(A2,B2)"
    ],
    [
        1,
        1,
        "=ADDRESS(A3,B3)"
    ],
    [
        4,
        2,
        "=ADDRESS(A4,B4,1)"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Row",
        "Column",
        "Address Result"
    ],
    [
        2,
        3,
        "=ADDRESS(A2,B2)"
    ],
    [
        1,
        1,
        "=ADDRESS(A3,B3)"
    ],
    [
        4,
        2,
        "=ADDRESS(A4,B4,1)"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Row",
        "Column",
        "Address Result"
    ],
    [
        2,
        3,
        "=ADDRESS(A2,B2)"
    ],
    [
        1,
        1,
        "=ADDRESS(A3,B3)"
    ],
    [
        4,
        2,
        "=ADDRESS(A4,B4,1)"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NjVmNDlmNDk4YzU1MjQ5ZWY2NTdlNDAzN2FhYmIwMDdlZTg3MzI4M2RiZjZlMGMyM2IxOTg0MTQyZTBmMjZhYzM4MjE0NTY3ZDVlZTA4ZTE0YjE1ZTNkYjExNzZkZmI0ZWJhMmFhNzBkNzFhY2M5MmNiZjhhYjg1YmQzMjY2NGUsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFF6TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Row",
        "Column",
        "Address Result"
    ],
    [
        2,
        3,
        "=ADDRESS(A2,B2)"
    ],
    [
        1,
        1,
        "=ADDRESS(A3,B3)"
    ],
    [
        4,
        2,
        "=ADDRESS(A4,B4,1)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub