Last Updated on October 31, 2023 by Jake Sheridan
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.
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.
Next, we’ll use the ‘&’ character to build our cell reference dynamically.
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.
Since we want to return the sum of the dynamic range, we’ll wrap the INDIRECT function with a SUM function.
You should now have a formula that uses a dynamic cell reference that has the final cell as a parameter the user can edit.
We can even create a dynamic cell reference where the first and last cells are dynamic as well.
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.
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.
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.
More Google Sheets collaborations:
- Insert Google Sheets Into Google Docs
- Insert Google Sheets Into Google Slides
- How To Share Google Sheets
- How To Comment
- Share Only One Sheet
- How To Access
- How To Save
- How To Give Access
- See Edit History
- How To Use Google Sheets Offline
- Track Changes
- Turn Off Overwrite
- How To Chat
- How To Tag Someone
- How To Share Only One Tab
- How To Collaborate
- Make A Google Sheet View Only
- Add Note
- Add Comment
- Link To Cells Or Spreadsheets
- How To Link Google Sheets
- Link Google Sheets Together
- Reference Another Sheet
- Unlink Form
- How To Hyperlink
- Create Hyperlinks
- Link Cells
- Reference Cells
- Use Absolute Cell References