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 function that DOES allow dynamic named ranges!

Drumroll please…

## 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 function 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?

### How does the formula for dynamic named ranges work?

- Formula to count last row
- Formula to create a string range reference
- This cell is a named range: dynamicRange
- SUM formula using INDIRECT and the named range
- SPARKLINE formula using INDIRECT and the named range

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

Very Nice!

Thanks for stopping by Yogi! Let me know what applications you find for this technique đź™‚

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

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

=vlookup is amazing

INDEX(MATCH) is 10^\infty times better

OFFSET(MATCH) is the best one đź™‚

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.

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

Hi Ben,

I am confused about creating the same behaviour for a column. Could you please clarify with an example? Thank you!

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.

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

Hello Ben thanks For your help.

I have a dynamic range :

â€śExpenses!â€ś&E1&â€ť3:â€ś&E&â€ť301â€ť

It reads out Expenses!F3:F301

Named it MyCosts

In the following formula it

Disregarded changes to E1 stuck in one column but does recompute changes in that column.

It refuses Indirect() as I have 3 parameters

I am trying to use it with this formula:

Sumif(MyCosts,â€ś=1â€ť,AL3:AL301)

Any help would be greatly appreciated. Berny

Ben, you’re actually not restricted to the sheet in which it is conceived. Just do everything you said above and then, in the second spreadsheet, nest two IMPORTRANGE formulas like so:

=IMPORTRANGE(“”, IMPORTRANGE(“”, “dynamicRange”))

The internal IMPORTRANGE retrieves the dynamic range from the first spreadsheet and then the external IMPORTRANGE uses the dynamic range to retrieve the data.

Ben –

Is there a way to create a dynamic range for a Pivot Table using this method or something similar?

I would also like something along these lines.

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

Exactly the answer I was looking for! Thanks

This is a great tip, thanks! Is there a similar way to expand selection if I add rows? In Excel I do named ranges with OFFSET function like

=OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

I’m also looking answer for this.

Ben, can you help us

Did anyone discover any better way to give dynamic data range for a pivot table

Hi Ben,

When I do this (leaving off the final row number in the range reference), I end up getting some blank spots and divide by 0 errors in my pivot table. Any way to remedy this?

Hey Justin,

Good question. Yes, apply a filter in the pivot table to remove them.

Cheers,

Ben

Using a filter to exclude the blanks creates another problem, which is that when new values are added, you then need to go into the filter and select the new value to get it to show in the pivot table. Is there any way to get around that which I am not aware of?

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

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

Awesome! I used two dynamic ranges to calculate Line of best fit. =Linest(INDIRECT(Yrange),(INDIRECT(Xrange))

Ah! Nice use case đź™‚

Hi.

Thanks for this. Big Help.

Going to the next level; Is there a way to put in a dynamic range for Data Validation ?

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 đź™‚

THAT was exactly what I needed, Thanks.

Hi Ben, dynamic ranges are ant!

But, how can I put my dynamic range into a dropdown list?

Remove my last comment, you already answered it!

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

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?

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.

And that limitation, along with VBA, keep me using Excel.

Is there any way to update the chart’s data range when new columns are added?

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.

wow

it awesome…

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.

Would I be able to make 2 dynamic named ranges and then use an equation to sum the cells between them?

Example:

=sum(NamedRange10:NamedRange11)

This works with normal ranges, but I cannot get it to work with your dynamic ranges.

Similar to Dustin Fransoo (above), Can I use a dynamic range in a named range? I have an employee sheet that varies as we gain/lose employees, and need a range that dynamically shrinks/grows as the number active rows changes. Thanks In Advance!

It helped me to improve by 80% the calculation time in a huge spreadsheet! Thanks for sharing such a nice knowledge!!

any update to using dynamic ranges in pivot tables?

I really want to get rid of the blank column!

or any other suggestion to getting rid of the blank column in the pivot table when referencing a set of data that is added to daily?

I’m having the same problem as others where my pivot table shows a row of zeros with a blank value in the row name. Is there any way to avoid this?

Hi Ben,

Got limited “Sheets” knowledge. Hope you will help. I have A2 pulling from a drop-down list of headers in a table in Sheet “Table.” There are 10 items made up of 3 different classes of items. Let’s say 5 clothing, 5 bags, and 2 jewelry items. I then use data validation in B2 to select a subset of 9 traits associated with each of the 3 classes from sheet “Array,” which uses a long formula of if(transpose),indirect() commands to build the traits for each item selected in the drop-down. How do I use your example to increment data validation to pull Array!A1:I1 into B2, incrementing to Array!A2:I2 in B3, and so on?

No need to reply. I posted in stackoverflow, and posted a response after finding exactly what I needed. Maybe it would be helpful to someone else. My post is at https://stackoverflow.com/questions/53604705/auto-increment-data-validation-in-google-sheets/53654965#53654965

I was looking for this functionality for months! Thanks!

Is there any way to change this formula to where A3:K becomes A4:K, A5:K, A6:K, etc. each time you drop a row in the spreadsheet.

Formula:

=ARRAYFORMULA(iferror (VLOOKUP (A2:A, A3:K, 2, FALSE), “In Production”))

I have a sheet for Technical Support Agents in my Company.

The sheet records their data and the compiles a reports using their input data.

Each of the Agents have their own sheet.

In the Report, the column showing their data is their name, which corresponds with the name of their sheet. (All in the same Workbook)

I would like to reference the sheet using the column name, however I’m getting a little bit stuck.

For example I have a Column and Sheet name Cohen

There is a Formula in a Cell that collects data from sheet Cohen to display under the Cohen Column.

Here is the Formula: (broken down for visibility)

=COUNTIFS ( Cohen!$K:$K , 12 ,

Cohen!$J:$J , $A44 ,

Cohen!$N:$N , “i” )

I would like the name of the Column to Dynamically Change between the sheet sheets, by replacing the column name. All the Ranges will remain the same.

Any ideas?

This is very nice, thanks. But for all the VLOOKUP fans, please learn INDEX/MATCH. You will never use VLOOKUP again!

Thank you for this post Ben. I am using your method to create dynamic named ranges. Do you know if there is a way to use that named range as the range for conditional formatting?

Any way to do discontiguous ranges this way?

Ultimately I’m wanting to do validation whose range originates with a filter. Validation allows for custom formulas, but when doing that no drop-down selections are offered.

Using INDIRECT is a workaround to the limitation that GS doesn’t allow for the use of formulas in the definition of a named range. Therefore unfortunately you can’t use this technique for referencing a named range in a data validation list.

Hi Ben,

I have tried using the dynamic range in getRange(), unfortunately I got Range not found error.

var lc=sheet1.getLastRow();

var clf = sheet1.getRange(‘A’&lc).getValue();

Is it possible to use the dynamic range for getRange() to execute a query in a loop?

Thank you so much! Love the example sheet.

Awesome idea Ben, thanks! However, I keep getting an error – would appreciate any help:

Trying to use several of these as part of a countifs and keep getting the error that “Error Array arguments to COUNTIFS are of different size”

Here’s the formula for reference: =countifs(indirect(bat_segment),$C4,indirect(bat_year),BN$1,indirect(bat_week),BN$2,indirect(bat_incomplete),False)

Thanks in advance!!

Hello, thank you for the tutorial. I followed the steps and can’t figure out why the formula is not working for me.

I’m calling the called reference from a Import Range Formula, it should works in the same way?

Thanks!

This is really cool! Thanks for sharing!

Hi Ben,

Would this work if I have one worksheet, on the tab labled Data I have 7 columns with headers, lets say Tom Frank Mary Jill Susie Eric George, under each person is a team of people. For example Tom would have a team of John Mark Luke Matt Ben. What I would like to do is on the second sheet, or the sheet that most people will use and see, when lets say Matt is typed into a cell, in same row it will automatically pick up Tom’s name and auto fill that. Is the process you describe here the best way to accomplish this?

Maybe it’s been asked (and answered) before but I can’t understand every question. I’m a beginner.

Here is my question: The range in your example is a2:a15. The 15 is defined by a result of a cell, but is it possible to modify the a2 dynamically also? I can’t seem to make it work; it gives me an error.

In short, I want to count the wins in a list of results (W or L) in a column for the last 10 games only without having to erase the previous games. How can I do that?

Example: the first 10 games of the season, it’s easy I go:

=countif(b5:b14,”w”)

With the dymanicRange, I did as you did =”nameofmysheet!B5:B”&K8 where k8 is my cell referencing to the last row where a game result is entered.

But when k8 is greater than 14 (example, 25) I’d like for my range to become b16:b25 . How can I write the formula so the B5 changes into B16 (where 16 will be k8-9).

Thanks!

I have many sheets from which i need to pull data based on the dates. I have used C5 cell as the input cell for sheet name which changes based on some conditions. I have come up with this formula…….=SUMPRODUCT(INDIRECT(C5&”!AY3:AY”=$F$3)*INDIRECT(C5&”!AQ3:AQ”=$B14)*1)

Where C5 is the sheet name and AY and AQ are the columns in the sheet referred in cell C5. I am getting this error…….”Function INDIRECT parameter 1 value is ‘FALSE’. It is not a valid cell/range reference.”

I require help in figuring out what is the error.

Thanks in Advance

You are so awesome! Thank you!

I should have posted this last year when I did the work but better late than never, eh?

I used your method as the basis for building a rather insanely complex spreadsheet for quoting customers. It uses something like 88 of these INDIRECT-based ranges, all of which look something like this:

=”Combinator!” &

REGEXEXTRACT(ADDRESS(2,MATCH(BJ$1,INDIRECT(solverFixturesHeader),0),4,TRUE),”\D”) &

solverFixturesRowStart &

“:” &

REGEXEXTRACT(ADDRESS(2,MATCH(BJ$1,INDIRECT(solverFixturesHeader),0),4,TRUE),”\D”) &

solverFixturesRowEnd

The formulas all have arrays built with nested INDIRECTs and it’s all rather dizzying and long-winded.

It also absolutely CRAWLS half the time and if my IMPORTRANGEs are feeling iffy the whole workbook is brought to its knees and I’m left smiling sheepishly at a customer.

But I probably wouldn’t have figured any of this out as quickly or easily if it wasn’t for your grain of inspiration and for that I’d like to offer my thanks!

Hi, Ben

Yes, this is a nice trick, but a very lame solution. Google Sheets should have this a part of its base code. Thanks for the solution, bad kid, Google Sheets!

Used the dynamic range idea in a dsum formula. Way Cool!! Thanks for the guidance.

Nice!

Hiya Ben,

I have a set of data that is dynamic, and I want to create a dynamic table in a google doc. Is there a way that if a set of data expands/contracts by row or col that the table in the google doc reflects this change?

Am stumped on this one…

Ta

Mik

Thanks much for this. Saved me a lot of time with the template. Feel free to add me to your mailing list if you have one.

Please, how can I use this formula with dynamic range?=query(GSC!A:Y&Setari!H12; “select A, W, X, Y, H”)

Thank you!

Hello,

Trying to write a countif formula where one of the columns are defined by a header that I put in a specific cell.

“=countifs(FC5:5,”>”&’O Over C Calc’!E$2,FC$3:$3,”high”)”

If I wanted ‘FC’ (this part ‘FC5:5’) to reference the column I have in A1 how would I do this?

Thanks anyone,

Nice tutorial, thank you!

One more thing that you can add: you still can’t use a dynamic range for chart input, but you can use the formula to copy a dynamic range to a fix position with:

=ARRAYFORMULA(INDIRECT(dynamicRange))

And then use that fix region as a chart input. It works best if the size of the range is fix.

Hey Ben!

Just wanted to point out a typo in your sample Sheet. In step 3 of sheet Example 1, you say to give the named range a name called ‘values’. I do believe that name should be ‘dynamicRange’.

Also, the range for that named range should be ‘Example 1’!C13 if I am not mistaken.

Thanks for the heads up, Ron! Fixed that now.

Many thanks. Saved me a lot of time!

Thank you Ben for this awesome hack.

I’ve a sheet where I need to update the daterange dyanmically, this hack helped me and now the formula works like charm.

Thanks again, keep inspiring us.

Hi, Ben,

Can you please tell me how to have a drop down show images further down the sheet? I have the images on another tab. Also, when I try to name the pictures to match the drop down names, I get an error because the names have numbers, I guess. Can you help me, please? Thank you!

Brilliant! This will save me a lot of time on maintenance of a sheet that is linked to a form.

Does this trick work in excel?

How would I create a dynamic range for a column but NOT the rows?

Despite it’s “work-around” nature, this is a far more elegant solution than anything I was able to come up with. I’ve watched many of your course videos in the past as well. Thank you for your terrific advice and expertise on this site!

great explanation. Thanks

Wow! thanks

I need to try it NOW!

I believe with the new Named Functions feature we don’t need this anymore. Instead we can create a function MYRANGE with INDIRECT pointing to a dynamic range “=INDIRECT(“Sheet1!A1:A”)”

+1 for the idea of revising this article now that Named Functions are here… but the way that Google have implemented them means that dynamic named ranges aren’t as neat in Sheets as in Excel.

Excel has a single Name Manager, and Names are parameterless unless invoked using LAMBDA (in which case they accept arguments in parentheses afterwards). Google’s approach is to have Named Ranges & Functions as two separate classes, but Named Ranges only accept static ranges (as has always been the case), and Named Functions must be followed by a parameter call.

The net effect of all this is that you can define a Named Function in Sheets to return a dynamic range using the same OFFSET or INDEX based constructions as Excel, but it always has to be followed by a ‘()’ as it is a function which requires no parameters (like the native PI() or EXP() functions). So a dynamic named range in Sheets always looks like a function rather than a Named Range, and doesn’t get listed in the Named Ranges box which would be the most logical place for it to go…

And it actually gets weirder than that if you import an Excel file into Sheets which contains dynamic named ranges (or indeed, any Name which doesn’t require parameters and doesn’t point to a static range); what happens is that Sheets imports it as a Named Function and WILL let you continue invoking the Name without the trailing parentheses, UNLESS you try to edit it, in which case it will then insist on the parentheses, and will break any formulas in the sheet which invoke it without them (TBF it does warn you of this)…

I can only speculate as to why, given the importance of Excel compatibility, Google chose to do things this way.

Interesting! Thanks to both of you for sharing your research.

I will look into this when I get a chance and update the article.

Hi Ben,

Thanks for this , but i needed your help how I can transfer the data to another sheets and then data will disappear form the original sheet where it came from . Not sure if I can use data validation for this or if i need to use Tool/ Script to do so

Thanks

Abee

Thank you very much Ben,

I recalled seeing this, and have been able to use it to auto-extend a Named range when a Query automatically adds rows to a sheet:

by adding =”DATA1!B11:M” to a cell, setting the Named range [_DATA1] to that cell, and then referring to the Named range by using: INDIRECT(_DATA1), in formulas… Thank you very much.

Marc

Thanks for the inspiration on this. This led me to use a Named Function that takes a range and turns it into a dynamic range string to ensure the dynamica range follows changes that affect the input range. It also extends to the last row in the input range sheet.

The string can in turn be used with INDIRECT() to output the range values where needed.

Formula:

=CELL(“address”, range)&”:”&SUBSTITUTE(ADDRESS(1,COLUMN(range)+COLUMNS(range)-1),”1″,””)&ROWS(INDIRECT(CELL(“address”, range)&”:”&SUBSTITUTE(ADDRESS(1,COLUMN(range)),”$1″,””)))+ROW(range)-1