Dynamic Named Range

[AZExcel]

I have used the following dynamic named range formula for quite some time to accomplish a dynamic range.


The only problem I have is I can't leave an empty cell between occupied cells. If I do the range is not accurate.


Does anyone have any recommendations apart from converting to a table?


=(OFFSET('Sheet1'!$E$3,0,0,COUNTA('Sheet1'!$E:$E)-1))

 

[dan_l]

I'll admit it. I have no idea why this works, but I know the technique:


=$A$1:indirect(ADDRESS(MATCH(9.99999999999999E+307,$A:$A),1))

 

[dan_l]

I should add:


I've seen that type of range blow up once in while. Make a backup, b'okay?

 

[AZExcel]

dan_I


Thanks I will give it a try

 

[AZExcel]

dan_I,


I think I get what you mean by blow up, the formula kept locking my computer up when I tried to work the range in a function.


Anyone else have a recommendation?

 

[vijaySharma]

Try this.. in the data validation list


=OFFSET($A$2,0,0,MATCH(2,1/(1-ISBLANK($A$2:$A$65536))))


~Vijay

 

[dan_l]

Maybe show us your data?

 

[Luke M]

=OFFSET(E3,0,0,MATCH("zzzz",E:E)-2)


The -2 is there since you are starting on row 3 (ignoring 2 rows)

 

[Hui]

AZExcel

If your columns is Text try: =OFFSET(Sheet1!$E$3,0,0,MATCH("*",Sheet1!$E:$E,-1)-2,1)

If your column is Numbers try: =OFFSET(Sheet1!$E$3,0,0,MATCH(1E+306,Sheet1!$E:$E,1)-2,1)


adjust the last -2 to be 1 less than the anchor row E3 in your case

 

[AZExcel]

Good Morning,


Thanks to all that replied, I will try all recommended solutions this afternoon and provide feedback. As always, this is the best source for excel information and help.

 

[AZExcel]

OK I tried all of the recommendations and they all provided the desired outcome.


I guess the moral of the story is that match works better than counta in this particular case.


I really appreciate the help on this.