# Need a little help with dynamic named range.

1. ## Need a little help with dynamic named range.

GOAL B: Define continuous named range (like A1:A25) based on keyword criteria. (provided that user takes care to make sure keywords are always in 1 cluster.) (with a formula)

Solution by : XOR LX (Though you'll probbaly need to read the thread to grasp it )

=INDEX(Sheet2!\$B\$1:\$B\$1000,MATCH(TRUE,INDEX(ISNUMBER(SEARCH("dog",Sheet2!\$B\$1:\$B\$1000)),,),0)):INDEX(Sheet2!\$B\$1:\$B\$1000,MATCH(2,INDEX(1/ISNUMBER(SEARCH("dog",Sheet2!\$B\$1:\$B\$1000)),,)))

What this does is search for "dog" in B1 to B1000 and provides the address of the first match then the last match. So a dynamic named range can be mapped based on the first address of the keyword (dog) and the last address of the keyword. So if B30 to B150 had dog in it - it would be mapped as a named ranged. You can later have the "block" of dog to B220 to B340 and this formula will still keep it mapped properly. Obviously you can offset it if you want the extra flexibility.

TAGS: Return cell addresses based on criteria, return first address of match, return last address of match.

2. ## Re: Need a little help with MATCH, INDEX, SEARCH and VLOOKUP.

little help provided

http://www.excelforum.com/the-water-...-question.html

3. ## Re: Need a little help with MATCH, INDEX, SEARCH and VLOOKUP.

Hi,

A sample workbook with out any confidential data showing before and after sheet will helps us to provide a fast & better solution .

Punnam

4. ## Re: Need a little help with MATCH, INDEX, SEARCH and VLOOKUP.

Allright, attaching file... Though I don't see how the question was confusing in text (;
Look for text match in 1 column - return list of cell addresses that can be used to define Named Range.

5. ## Re: Need a little help with MATCH, INDEX, SEARCH and VLOOKUP.

I don't see the desired (expected) result in your sheet.

Why not use a filter (on column B)?

6. ## Re: Need a little help with MATCH, INDEX, SEARCH and VLOOKUP.

@ Oeldere,

Polymorpher need a name range which should updated based on words WOODMINE,STONEMINE,FARM & IRONMINE . etc

Punnam

7. ## Re: Need a little help with MATCH, INDEX, SEARCH and VLOOKUP.

Obviously if i had a way to make a dynamic range for "woodmine" i'd just replace the word to "whatever" and use the same thing for the other ranges (: The point was to find a way to do that. Google threw out a lot of things for "ëxcel dynamic named range" but nothing I was able to understand well enough to adapt for myself.

8. ## Re: Need a little help with dynamic named range.

Alright i have made some progress after a google carnage.. Now the hell if i know how to fit this into the Named Range box. Somebody help me with the final lap here....

B10 to B19 has "keyword" in every cell.

Option 1:
Option 2:
Option 3:

Option 1:
Option 2:
Option 3:

Now I finally have the darned addresses and can't use them ...
(Now obviously these aren't utterly dynamic, but dynamic enough i guess)

Sigh

For future reference be warned! Probably none of these can compensate multiple rows or scattered named ranges such as A1, B2, C1:C10... Etc. (I havent needed them to - its a real pain to even get so far)

SEO:

Excel find / return first match address. Excel find / return last match address.

9. ## Re: Need a little help with dynamic named range.

why do you need a named range? what is the purpose of it? what are you going to do with it once you have it?

10. ## Re: Need a little help with dynamic named range.

Here's what i do with it:

IF(\$D\$1>1,OFFSET(INDEX(NAMEDRANGE,3),,-1))

Basically - if condition met, give me the cell left from the third (top to bottom) cell in the named range)

And I repeat that a lot.

Like : =IF(M20>1,OFFSET(INDEX(NAMEDRANGE,3),,-1)&OFFSET(INDEX(NAMEDRANGE,5),,-1))

11. ## Re: Need a little help with dynamic named range.

i dont think you can use non contiguous ranges like that

12. ## Re: Need a little help with dynamic named range.

Originally Posted by martindwilson
i dont think you can use non contiguous ranges like that
You can INDEX non-contiguous ranges, provided you use INDEX's fourth parameter (area_num) to let Excel know to which of the ranges you're referring:

=INDEX((\$B\$12:\$B\$13,\$B\$27,\$B\$43:\$B\$44,\$B\$48:\$B\$52),1,1,1)

would refer to cell B12.

=INDEX((\$B\$12:\$B\$13,\$B\$27,\$B\$43:\$B\$44,\$B\$48:\$B\$52),2,1,4)

would refer to cell B49.

etc.

Regards

13. ## Re: Need a little help with dynamic named range.

I know, Right now I'm trying to make to continuous ones work for starters...
As for scattered named ranges (not with what I have) but normally you can. Just multi-select random cells (non continuous) and bind a name to in the standard way up in the name box.

If i get them to work I can loop enough IFS to make the non continuous ones work too (or so i think).

Its enough of a pain in the *** to get the last address - you need to close a continuous range.

Its not entirely hard to map scattered ranges if only you could return the address of every match based on search criteria. But that apparently is too much to wish for without VBA.
Its easier for me to re arrange the documents so the ranges are continuous than to explore that headache.

14. ## Re: Need a little help with dynamic named range.

i know about that index function but not in this usage, what is the ultimate goal? there is probably another way to do it

15. ## Re: Need a little help with dynamic named range.

Originally Posted by martindwilson
i know about that index function but not in this usage, what is the ultimate goal? there is probably another way to do it
I confess that I got lost after trying to read a few of the posts so to be honest I have no idea what the goal here is...

Regards

16. ## Re: Need a little help with dynamic named range.

Told ya:...

If the 3rd (top to bot) cell of range "animals" has a "Dog "

The 4th (top to bot) of range "actions" has "ate "

And the 8th (top to bot) of range "objects" has "my sandwitch"

Then I call for:

=INDEX(animals,3)&INDEX(actions,4)&INDEX(objects,8)
And it spits out "Dog ate my sandwitch"

(this is irrelevant if the range is continuous or not, you ask for a specific cell count in a range)

Simple as that...

17. ## Re: Need a little help with dynamic named range.

Originally Posted by Polymorpher
Told ya:...

If the 3rd (top to bot) cell of range "animals" has a "Dog "

The 4th (top to bot) of range "actions" has "ate "

And the 8th (top to bot) of range "objects" has "my sandwitch"

Then I call for:

=INDEX(animals,3)&INDEX(actions,4)&INDEX(objects,8)
And it spits out "Dog ate my sandwitch"

(this is irrelevant if the range is continuous or not, you ask for a specific cell count in a range)

Simple as that...
Is this example relevant to the attachment? Or will a solution to this new, (hypothetical) question be of use to you? If so, perhaps you should post a reduced, simplfied version of your eariler attachment with an example such as the one you give above.

Regards

18. ## Re: Need a little help with dynamic named range.

Allrighty here's the sheet with what I'm talking about in the last posts:

Although its explained pretty completely.

Use sheet 2

19. ## Re: Need a little help with dynamic named range.

GOAL A: Define non continuous (scattered like A1, B3:B6, C10, G12:G22) named range based on keyword criteria. (with a formula)
GOAL B: Define continuous named range (like A1:A25) based on keyword criteria. (provided that user takes care to make sure keywords are always in 1 cluster.) (with a formula)

Also updated in main post.

20. ## Re: Need a little help with dynamic named range.

Still not sure what you mean:

"Sheet2! Start address = first MATCH for the word "dog" : end address the last MATCH for the word "dog""

First and last matches in which column? Column B?

If so, this will give you the required Named Range for animals (amend the 1000 to a suitably higher row number if required, though I strongly recommend you don't use B:B in this construction):

=INDEX(Sheet2!\$B\$1:\$B\$1000,MATCH(TRUE,INDEX(ISNUMBER(SEARCH("dog",Sheet2!\$B\$1:\$B\$1000)),,),0)):INDEX(Sheet2!\$B\$1:\$B\$1000,MATCH(2,INDEX(1/ISNUMBER(SEARCH("dog",Sheet2!\$B\$1:\$B\$1000)),,)))

Regards

21. ## Re: Need a little help with dynamic named range.

Holy sh**gumballs thats working...
And the B:B is not a problem - My max range is like 1800... I will always do B1:B1900.

It would have taken me weeks to craft that formula.

Thanks for the patience. And riddle solver.

That covers Goal B.

I imagine the guy who solves Goal A has to ask for a nobel prize.
As the range there is neither in 1 column neither a continuous set of cells.
So each individual address that has a MATCH has to be returned to form a named range.

22. ## Re: Need a little help with dynamic named range.

You're welcome!

Since I'm obviously having difficulty understanding, can you just clarify what Goal A should be in terms of this new attachment with "dog", etc.?

Cheers

23. ## Re: Need a little help with dynamic named range.

Use the first sheet, GOAL A is to find a forumla that can make a named range out of "IRONMINE" keyword.

Except this time the range as you can see is scattered. (still in a single column - again B).
The non dynamic version of this looks like the currently working WOODMINE range.

(yes martin - that was still included in the latest XLS as Sheet 1

24. ## Re: Need a little help with dynamic named range.

still not sure but maybe

25. ## Re: Need a little help with dynamic named range.

Creating such a non-contiguous Named Range without VBA is practically unfeasible, at least in the sense that it would be no more dynamic than if you were to create it by manually selecting each of the required ranges in turn.

Regards

26. ## Re: Need a little help with dynamic named range.

Yeah I kind of figured as much by now, It took me 4 days to only fail at making the range you did.

Thanks again, thread solved and cleaned up for future use.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1