Creating Dynamic Named Range in a Row

**Quentyn** · 08-20-2011, 04:47 PM

Currently, I have range C3:AJ3 named as "MyRange". A3 and B3 are being used as headers.

I'd like my named range in row 3 to dynamically expand when inserting new columns.

I've been working on this for a bit and I am stumped on what formula to use.

Thanks in advance for your help.

**ZENITH** · 08-20-2011, 05:08 PM

This is what I got:

=OFFSET(Sheet1!$C$3,0,0,1,COUNTA(Sheet1!$3:$3))

put that in the Refers to: section of your named range

**JBeaucaire** · 08-20-2011, 05:16 PM

Close, Zenith, you forgot to remove A:B from the count:

=OFFSET(Sheet1!$C$3, 0, 0, 1, COUNTA(Sheet1!$3:$3)-2)

**MarvinP** · 08-20-2011, 05:27 PM

Hi Quentyn,

=OFFSET(Sheet1!$C$3,0,0,1,MAX(MATCH(1000,Sheet1!$3:$3,1)))

did the trick for me.
See the example sheet with this in it.
I used 1000 as the Maximum column you would have data in. If it might be larger then increase 1000 to 10000.

Give all our examples a test. Put numbers and text in row 3 to see if the dynamic range still works correctly.

Then read http://www.ozgrid.com/Excel/DynamicRanges.htm

**ZENITH** · 08-20-2011, 05:54 PM

Yeah I always forget to subtract the extra garbage :P.

**Quentyn** · 08-21-2011, 09:50 AM

All,

Thanks so much for your help.

Originally Posted by MarvinP

Then read http://www.ozgrid.com/Excel/DynamicRanges.htm

Thanks Marvin. That is actually the site I was working from, and I've learned a lot, both there and here. Where I was stumped was the values to use with the count function. I'm using count as the entries are strictly numeric. I can see the solution was simpler than I was trying to make it.

Thanks again everyone.

**MarvinP** · 08-21-2011, 12:59 PM

Hi Quentyn,

The problem got harder if you insered a blank column or two. If the DNR only counted numbers then it would be blank cells short from the last column to sum. The CountA function counts the number of cells that aren't empty. You could also use Count() which only count numbers in the range. Both give you problems when trying to use them in a DNR. You really wanted the last column number that had anything in it. That is where the Max(Match()) part of my formula came from.

Then to be better at defining the DNR, because you started in Column 3 instead of 1, I should have subtracted a few like Jerry did.

I'm glad you found an answer.