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.
Category
Lookup and reference
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 stringB2
,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 becauseINDIRECT
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('NjkxZmI1NzM5MTYxNDUwZGQwYzNmODlhOTJjNTkxZTdjODZkN2U1Y2VhOTM3ZDU2ZTc0OTdlMjE4MGY0Y2RhOWM5MzAzYzQ3MDU4OTFjZWMyY2U5ZmQyNjI4ODczMmM5NDBhMTYzMjg5ZDk0NjFmODNmYWYyZDgyOTQ0MTlhNzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPREExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('NjkxZmI1NzM5MTYxNDUwZGQwYzNmODlhOTJjNTkxZTdjODZkN2U1Y2VhOTM3ZDU2ZTc0OTdlMjE4MGY0Y2RhOWM5MzAzYzQ3MDU4OTFjZWMyY2U5ZmQyNjI4ODczMmM5NDBhMTYzMjg5ZDk0NjFmODNmYWYyZDgyOTQ0MTlhNzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPREExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('NjkxZmI1NzM5MTYxNDUwZGQwYzNmODlhOTJjNTkxZTdjODZkN2U1Y2VhOTM3ZDU2ZTc0OTdlMjE4MGY0Y2RhOWM5MzAzYzQ3MDU4OTFjZWMyY2U5ZmQyNjI4ODczMmM5NDBhMTYzMjg5ZDk0NjFmODNmYWYyZDgyOTQ0MTlhNzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPREExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('NjkxZmI1NzM5MTYxNDUwZGQwYzNmODlhOTJjNTkxZTdjODZkN2U1Y2VhOTM3ZDU2ZTc0OTdlMjE4MGY0Y2RhOWM5MzAzYzQ3MDU4OTFjZWMyY2U5ZmQyNjI4ODczMmM5NDBhMTYzMjg5ZDk0NjFmODNmYWYyZDgyOTQ0MTlhNzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXdPREExTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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\")"
]
]
}]
});
}
}