INDIRECT function

PRO

The INDIRECT function in Jspreadsheet Formulas Pro is a useful tool that allows you to retrieve the value from a specific cell, defined by a text string. The text string essentially acts as a cell address, telling the function exactly where to look. For example, if you input INDIRECT("A1"), the function will return the value in cell A1. This powerful function provides flexibility in dynamically referencing various cells in your spreadsheet.

Documentation

Returns the reference specified by a text string.

Syntax

INDIRECT(ref_text, [a1])

Parameter	Description
`ref_text`	A cell reference, defined as a string.
`a1`	Optional. A logical value that specifies what type of reference is contained in the cell ref_text. If omitted, it defaults to TRUE, meaning that A1-style references are used.

Behavior

The INDIRECT function is used to convert a text string into a cell reference. The main behaviors of this function are as follows:

If the cell reference is a text string in a cell, INDIRECT will retrieve the value from the cell referenced by the text string. For example, if cell A1 contains the text string B2, INDIRECT(A1) will return the value in cell B2.
The INDIRECT function treats empty cells as zero values.
If the cell reference is a text string, INDIRECT will treat it as a cell reference. For example, INDIRECT("A1") will return the value in cell A1.
The INDIRECT function will return an error if the text string does not correspond to a valid cell reference.
Booleans and errors are handled as usual, meaning if the referenced cell contains a boolean or an error, the INDIRECT function will return that boolean or error.

Common Errors

Error	Description
#REF!	This error is displayed when the cell reference is not valid. This could be because the cell does not exist, or the text string does not correspond to a valid cell reference.
#VALUE!	This error is displayed when the formula includes cells that contain different data types.

Best practices

Be careful when using INDIRECT in large spreadsheets, as it can slow down the calculation speed. This is because INDIRECT is a volatile function, meaning it recalculates every time a change is made anywhere in the workbook.

Try to keep your cell references simple and clear to avoid confusion and potential errors.

Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself.

Remember that INDIRECT does not work with references to cells in other workbooks that are closed. In such cases, use direct cell references or find another workaround.

Usage

A few examples using the INDIRECT function.

INDIRECT("A1") returns 5, assuming cell A1 contains the value 5  
INDIRECT(C4) returns the value in cell A1, assuming cell C4 contains the value "A1"   
SUM(INDIRECT(A3)) returns the sum of the range B6, assuming cell A3 contains the value "B6"

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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "A1",
        "B1",
        5
    ],
    [
        "A2",
        "=INDIRECT(A1)",
        10
    ],
    [
        "B1:B3",
        "=SUM(INDIRECT(A3))",
        15
    ],
    [
        "Product Sales",
        125,
        "=INDIRECT(\"B2\")"
    ]
]
  }]
});
</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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "A1",
        "B1",
        5
    ],
    [
        "A2",
        "=INDIRECT(A1)",
        10
    ],
    [
        "B1:B3",
        "=SUM(INDIRECT(A3))",
        15
    ],
    [
        "Product Sales",
        125,
        "=INDIRECT(\"B2\")"
    ]
];

    // 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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "A1",
        "B1",
        5
    ],
    [
        "A2",
        "=INDIRECT(A1)",
        10
    ],
    [
        "B1:B3",
        "=SUM(INDIRECT(A3))",
        15
    ],
    [
        "Product Sales",
        125,
        "=INDIRECT(\"B2\")"
    ]
]

        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('NjJlZjE0YTMzODMzYTJlMDEwNDc5NmVlZDJhYTA1MzgxMzc2ZDdkZDBiNmYyZGUwN2QwNGIwMDkzNTAxY2UxNDJhYmVmOWI2NGNiMzExODJiMDM0NjMxMjA4NzNlMGY4MzFiNjJjODU4NjcxYTEyNjU1MjJkNjIwMmU1MjNiMGMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBOekExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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: [
    [
        "A1",
        "B1",
        5
    ],
    [
        "A2",
        "=INDIRECT(A1)",
        10
    ],
    [
        "B1:B3",
        "=SUM(INDIRECT(A3))",
        15
    ],
    [
        "Product Sales",
        125,
        "=INDIRECT(\"B2\")"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub