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…

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

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

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

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

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

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.

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! 👍