How to create dynamic named ranges in Google Sheets

I’d always believed that Named Ranges in Google Sheets would only accept static ranges and not formulas like Excel, which makes them less powerful of course. (Check out the Named ranges sidebar and you’ll see what I mean. No place to add formula-based dynamic ranges in there.)

However, there is a clever trick using the INDIRECT formula that DOES allow dynamic named ranges!

Drumroll please…

Dynamic named ranges in Google Sheets

Using formulas in named ranges to make them dynamic

Step 1: Create a formula whose output is a range reference in string format, for example:

="Sheet1!A1:A"&B1

where cell B1 contains an integer that we control (typed or from a calculation).

So if cell B1 contains the integer 500 say, the output of this formula would be a text string as follows:

Sheet1!A1:A500

Step 2: Create a named range for this cell with the string in.

Click on the formula cell, click on Data > Named ranges... and give it a name, e.g. dynamicRange

Step 3: Combine this named range with the INDIRECT formula to refer to this string range inside your other formulas, which gives you a dynamic named range.

For example, to create a dynamic sum we could do this:

=SUM(INDIRECT(dynamicRange))

Or a dynamic sparkline:

=SPARKLINE(INDIRECT(dynamicRange))

Once these are setup, if you go back and change the value in cell B1, the range will update and the formulas in Step 3 will also update.

Can I see an example worksheet?

Yes, here you go.

How does the formula for dynamic named ranges work?

dynamic named ranges explanation

  1. Formula to count last row
  2. Formula to create a string range reference
  3. This cell is a named range: dynamicRange
  4. SUM formula using INDIRECT and the named range
  5. SPARKLINE formula using INDIRECT and the named range

Dynamic named ranges! Woohoo! This is one of my all time favorite Google Sheet tricks.

26 thoughts on “How to create dynamic named ranges in Google Sheets”

  1. I am trying to use method 1 in VLOOKUP, but its not working. I want to give range as sheet ‘1’ column A through R(‘1’!A:R). VLOOKUP gives out of bound range error when I replace the input with formula

    1. Hi Rachit,

      I tried this with a sheet labelled ‘1’ and my vlookup formula looked like this:

      =vlookup(A1,'1'!A:R,2,false)

      It worked fine when I tried, so not sure what’s going wrong with your formula. Have you tried to return a column outside of the range A:R, by using an integer beyond 18 as your third argument in the vlookup? That would give an out of bounds for example.

      Cheers,
      Ben

  2. Is there a way to make the used columns dynamic as well, not just the rows? Considering that most likely I determine a column as a number, not a letter.

    1. Hey Johannes,

      For a column example, you’d replicate the steps but use a formula like this one to create the dynamic range:

      =ArrayFormula(SUBSTITUTE(ADDRESS(1,MAX(IF(1:1<>0,COLUMN(1:1))),4),"1",""))

      Then your next formula would be something like this:

      ="Sheet1!A1:"&C6&"1"

      which gives you the same behaviour but for a column.

      I don’t see any reason why you wouldn’t be able to combine dynamic row and column references into a formula. Your final formula would look something like this:

      ="Sheet1!"&A1&A2&":"&B1&B2

      where all four row/columns references pull in from cells.

      Hope that helps!

      Cheers,
      Ben

  3. If you try to use the named range in another sheet in the same document, it doesn’t work. Is there any way around that? I thought named ranges crossed sheets.

    1. Hey Dave,

      Any named ranges are accessible anywhere in the Google Sheet in which they were created. You can also transfer named range data from one sheet to another using the IMPORTRANGE function like so:

      =importRange("Google Sheet URL","Named Range name")

      Unfortunately, you can’t transfer a reference to cells from one Sheet and use the indirect in a different Sheet with that reference, so this dynamic named range is concept is restricted to the Sheet in which it is conceived.

      Cheers,
      Ben

    1. Argh, unfortunately not as far as I know. We can’t use named ranges as data sources for pivot tables, only regular A1 ref notations.

      You can make pivot tables auto-update when new data is added to your datasets however, by simply leaving off the final row number in your range reference. So make sure your pivot table is based on data in e.g. A1:E (you’ll notice the tool then defaults to adding 1000, or whatever the bottom row in your sheet is, to this reference).

      Any new rows of data will be included in your pivot table then.

      Hope that helps!

      Ben

  4. Ben,

    What (if there is any) would be a good way to use multiple Dynamic Ranges in a single sheet? I need to create dynamic ranges for different sections of a table, but each range needs to use the same column (just different sections of the column). Is there any way to create some sort of break statement?

    Thanks

    1. Hey Alex,

      There’s no way to create a “break” statement, but since you specify the range boundaries dynamically (by linking to a cell with a value in, which can be changed), you can just create separate dynamic ranges by choosing these variables correctly, right?

      e.g. ="Sheet1!A1:A"&B1

      and put a data validation on B1 to stay below 100 say.

      Then dynamic range two could start at A101, like so:

      ="Sheet1!A101:A"&B2

      and B2 with a data validation must be greater than 101.

      Does that work?

      Cheers,
      Ben

    1. If you reference an open-ended range in your data validation (e.g. A:A) then anytime you add new entries they’ll just appear on your data validation list 🙂

  5. Hey, thanks for this nice tutorial. Small question here : is it possible to directly use the created range without naming it ?

  6. Hi Ben,

    Any way to use the named range as a reference for a “data range” for use in a chart? If not, any workarounds to dynamically changing the range in a chart?

    1. Unfortunately you can’t use named ranges as data ranges for charts. In the very simple case, when you highlight a range of data for your chart that includes blank rows, if data then populates those rows in the future, it’ll automatically show up in the chart.

  7. Ben,
    How can i prepare a drop-down list using importrange datas.
    E.g. If i want select a particular branch say “x” it should show its datas by importrange method, and if i select branch “y” it should display its data by importrange.

  8. Can I not just create a data validation with the criteria being a List of Items type and the and the cell range just be $ and the starting cell?

    For example, I have a shopping list. I want to add a category column with a drop down choice of specific categories (for sorting purposes later and avoiding variants). Assuming the category column is Column C, I want to start in row 4 because of various headers above, and the name of the sheet is “Shopping”, can I not just use cell range of Shopping!C$4, then Criteria type of List of Items? This seems pretty straight forward, but I cannot get it to work.

Leave a Reply

Your email address will not be published. Required fields are marked *