Dynamic range name when it starts in a row > 1? Counta() does not work for me!

[actjfc]

Excel friends,

I would like to setup a dynamic range name for B9:C20. I have tried out too many online examples using Counta(), but those examples do not work for me. B9:C20 starts in row 9, and it is two columns wide. Variations of this formula do not work for me: OFFSET($B$9,0,0,COUNTA($C:$C),1)

Can somebody help me out?

Thanks!

 

[Glove_Man]

The Final 1 is what's doing you in here. Change it to a 2 and you should be all good.

OFFSET($B$9,0,0,COUNTA($C:$C),2)

Though be aware, this will count all entries anywhere in column C. Is that what you want? You might prefer something more like:

OFFSET($B$9,0,0,COUNTA($C9:$C20),2)

That arrangement will check how many entries are in C9:C20 and then return you that many in the dynamic range.

 

[actjfc]

Thanks, it works, but not 100% as I need! Yes, I want it to count all entries in column C. This range feeds a vlookup range that must dynamically increase in size if it is extended beyond C20+. When I setup the range name it was highlighting a range that was beyond C20+ for a few rows. I realized that subtracting the count of blanks that are up in C1:C8 will fix it. So, OFFSET($B$9,0,0,COUNTA($C:$C)-COUNTIF($C$1:$C$8,""),2) works perfect now . Thanks for your help, the "2" was the key to find my answer.

 

[Glove_Man]

Glad it worked.

"Active" blank cells are nuisance