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

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 this formula 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