How to use INDIRECT function for dynamic linking in Excel

Imagine you are working with an Excel workbook containing numerous sheets. In older versions of Excel, the maximum number of sheets allowed in a workbook was 255, while in newer versions, it is only constrained by the computer's available memory. You need to retrieve values from a cell on another sheet within the same workbook.



Example of the INDEX Function

Formula Example

Suppose you know the sheet's name (March) and it is entered into cell B1. You can easily accomplish this task using a formula that combines two simple functions:

  1. INDEX
  2. CONCATENATE

For this example, let's illustrate this situation and its solution through images:

Argument March

On this sheet, the values from specific cells on other sheets in the Excel workbook are displayed.

To display the first value from the "March" sheet, specifically cell C3, in cell B2 of the main sheet, enter the formula:

=INDIRECT(CONCATENATE(B1,"!C3"))

As a result, we get the value of cell C3 from the "March" sheet:

CONCATENATE

You can also include not just the link to the sheet in the CONCATENATE function's arguments but also the link to the cell that contains the name of that sheet. This way, the entire address can be dynamically changed as needed based on the sheet names and their cell addresses. To achieve this effect, you need to modify our formula slightly:

  1. Insert a new row between the first and second rows. To do this, select the entire second row, press the CTRL+SHIFT+= key combination, or right-click the header of the second row and choose the "Insert" option from the context menu.
  2. In cell B2, enter the value C3.
  3. In cell B3, modify the formula, specifically the arguments of the CONCATENATE function:
=INDIRECT(CONCATENATE(B1,"!",B2))

Now the formula operates in a fully dynamic mode:

INDEX

Of course, the capabilities of this formula cannot be fully described in a single article. The main goal of this tutorial is to demonstrate how you can dynamically change cell references, even to different sheets and workbooks.

Explanation of How the Formula for Dynamic Link to an Excel Sheet Works:

The INDEX function allows you to transform a text value into a link. In its first argument, we use the CONCATENATE function. It enables us to construct a dynamic link by combining multiple arguments inside the function. The optional second argument for INDEX allows us to specify the addressing style:

  • 1 - Classic (e.g., A1)
  • 0 - R1C1 style (e.g., R1C1)

Returning to the CONCATENATE function, it is designed to concatenate multiple text parts into a single text string. In this example, the CONCATENATE function builds a link with three text parts (in this case):

  1. The name of the sheet (March)
  2. An exclamation mark (!), which is a required symbol for creating a reference to another sheet.
  3. The necessary cell (C3).

Instead of sheet and cell names, we specify B1 and B2 in the formula, allowing us to dynamically change the link's address and, consequently, the value returned by the formula.

Useful Tip! For many Excel users, it is more convenient to use the ampersand symbol (&) instead of the CONCATENATE function. In this case, our formula would contain only one function:

=INDIRECT(B1&"!"&B2)
Ampersand

This method is faster and especially suitable when you need to concatenate text strings from more than 255 parts. However, the first option is more readable.

Formula Example

download file Download