Published on October 31, 2023 by Jake Sheridan
In this tutorial, you will learn how to use dynamic cell references in Google Sheets.
What are Dynamic Cell References in Google Sheets?
Cell references are essential in most Google Sheets formulas. But what do you do when you have a set of data that constantly changes? This is where dynamic cell references come in handy. They automatically adjust based on the size of the data you are working with.
Dynamic cell references are also useful if you want the cell references to adjust based on specific data. For example, you may want to quickly replace the sheet name in an external cell reference.
By using simple concatenation, we can build a string that serves 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 you can create a dynamic named range in Google Sheets.
Step 1
First, let’s add a cell in our spreadsheet for the user to input the last row to include in the dynamic range.
In this particular example, let’s indicate that we want the range to end at the 10th row.
Step 2
Next, we will use the ‘&’ character to build our cell reference dynamically.
Step 3
Then, we will 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
As we want to return the sum of the dynamic range, we will wrap the INDIRECT function with a SUM function.
Step 5
You should now have a formula that uses a dynamic cell reference with the final cell as a parameter that can be modified by the user.
Step 6
We can even create a dynamic cell reference where both the first and last cells are dynamic as well.
Step 7
Dynamic cell references are also useful when 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 titled “Week 1” and “Week 2”. Both sheets include a single table with 15 values in the range A2:A16.
We want to dynamically find the sum of either of these tables.
Step 8
We will first concatenate the provided sheet name with the string “&!A2:A16” to create a valid cell reference. Then, we will wrap the cell reference with the INDIRECT function to return the actual values of the range. Finally, we will 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 also change.
Summary
This guide should explain everything you need to know about using dynamic cell references in Google Sheets. You can make a copy of this example spreadsheet to test it out for yourself. Don’t forget to check out Crawlan.com for more tips and tricks on Google Sheets. Let your creativity guide you and use these dynamic cell references to simplify and automate your tasks in Google Sheets!
