How To Use Dynamic Cell References in Google Sheets

In this tutorial, you will learn how to use dynamic cell references in Google Sheets.

How To Use Dynamic Cell References in Google Sheets

Cell references are an essential aspect of most Google Sheets formulas.

But what if you have a dataset that constantly changes?

You may want to use a dynamic cell reference, which will automatically adjust to the size of the data you are working with.

Dynamic cell references are also useful if you want cell references to adjust based on certain given data. For example, you may want to quickly replace the sheet name of an external cell reference.

Using simple concatenation, we can construct a string that works as a dynamic cell reference. We can use the INDIRECT function to return the referenced data in another cell.

In this guide, we will show you how to create a dynamic cell reference in Google Sheets.

How to Create a Dynamic Named Range in Google Sheets.

Here’s how to create a dynamic named range in Google Sheets.

Step 1

First, we’ll add a cell in our spreadsheet for the user to indicate the final row to include in the dynamic range.

In this particular example, we’ll start by indicating that we want the range to end at the 10th row.

Step 2

Next, we’ll use the ‘&’ character to build our cell reference dynamically.

Step 3

Next, we’ll use the INDIRECT function to return the values indicated by the cell reference.

The INDIRECT function works by returning the reference specified by a specific text string.

Step 4

Since we want to return the sum of the dynamic range, we’ll wrap the INDIRECT function with a SUM function.

Step 5

You should now have a formula that uses a dynamic cell reference that has the final cell as a parameter the user can edit.

Step 6

We can even create a dynamic cell reference where the first and last cells are dynamic as well.

Step 7

Dynamic cell references are also helpful if you find yourself in a situation where you need to switch between similar data stored in different sheets.

In this example, we have two sheets labeled ‘Week1’ and ‘Week2’. Both sheets include a single table with 15 values in the range A2:A16.

We want a dynamic way to find the sum of either of these tables.

Step 8

We’ll first concatenate the provided sheet name with the string ‘!A2:A16’ to create a valid cell reference.

We’ll wrap the cell reference with an INDIRECT function to return the actual values found in the range. We’ll then wrap the INDIRECT function with SUM to return the actual total of the range.

If we try to change the sheet name, the range used in the SUM function will change as well.

Summary

This guide should be everything you need to use a dynamic cell reference in Google Sheets.

You may make a copy of this example spreadsheet to test it out on your own.