Dynamic Named Ranges

But here is my problem, I'm having trouble trying to get my named range function created - thanks to Gabhan Berry - ("GreaterThan") to work since my Dynamic Named Range (the "client" column) contains blank rows, and when I try to reference it within a formula it doesn't work. I've also tried a function - created by Jon Acampora - called "FindLast"

The formula I see for Clients

Formula:

=OFFSET(Sheet2!$B$2,1,0,COUNTA(Sheet2!$B:$B)-1)

would have that drawback with respect to blank rows. Though very popular I've never been a fan of COUNTA constructions in DNRs for that very reason.

Try this alternative for text DNRs.

Formula:

=$B$3:INDEX($B:$B,MATCH("zzzzzz",$B:$B,1))

It will always match on the last row of data even if there are blanks in the range. Please note that the match type is 1 for approximate match.

If you should ever have occasion to need a DNR for numbers

Formula:

=$B$3:INDEX($B:$B,MATCH(1E+306,$B:$B,1))

I've never seen or Googled Jon Acampora's - "FindLast". Perhaps it's the same formula.


I hope this helps.