Non-Contiguous Named Range?

[Ray]

Hi -

I've created one dynamic named range to hold all values from cell D2
to last cell in column (so if user adds another value to bottom, named
range extends automatically to include that value).

Now, I'd like to create another named range which is a subset of the
main named range ... this subset would include all values which start
with "escrow". Is there a way to build a named range like this? The
values won't necessarily be contiguous (ie, user adds a new 'escrow'
value to bottom of column and doesn't re-sort the whole list).

Thanks, ray

 

[Jarek Kujawa]

only solution i might think is via creating a macro

may i ask why/what do you need that subset for?

 

[Héctor Miguel]

hi, Ray !

if you let me suppose... and what you *really need* is a sum from next column to named range
but only where each row in the named range begins with "escrow" -???-

you could try with the following "array formula" (confirm by ctrl+shift+enter NOT just enter)
replace <name> with your name-range (and watch for line-wrapping, it should be in a single line)

=sum(subtotal(9,offset(name,small(if(isnumber(search("escrow*",name)),row(name)),row(indirect("1:"&countif(name,"escrow*"))))-2,1,1,1)))

be aware of "-2" in the formula, it shall be the starting row-number of your named-range

if any doubts (or further information)... would you please comment ?
hth,
hector.

__ OP __