Make a Named Range dynamic without $

[krislopmar]

Hello, Im trying to create a dynamic named range avoiding $, but it doesnt work. The range is in another sheet, how can I make it so that when I add a row it includes it in the range making it 1 row bigger?


Thank you!

 

[bobhc]

Good day krislopmar

You will find the linkl very helpful, what you want is towards to bottom of the page


http://www.contextures.com/xlDataVal02.html#Name01

 

[jskushawah]

you can avoid $ by using named ranges.

 

[Debraj]

Hi Krislopmar,


long time... welcome back..

But, after 8 month, you came back and that's also with approx same question..


You can increase the area of NameRange by INSERT any Row/Column in between..


If you ADD a row at the bottom of the NameRange (I mean outside of the NameRange),Excel wouldn't consider it.

But if you Insert any Row/Column in between the NameRange.. EXCEL will automatically adjust it for you..


Regards,

Deb

 

[Luke M]

If you do want to ADD at the end of the range, you might do something like:

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


This will return the range equal in size to amount of data in col A and as wide as you have headers in row 1.


EDITED: $1:$! by $1:$1 (SirJB7)... Luke M: check and clear, thanks.

 

[SirJB7]

Hi, krislopmar!

I agree with Debraj Roy's words... so long... and almost same issue... and we don't even know if you succeeded in your first attempt...

Regards!