+ Reply to Thread
Results 1 to 26 of 26

Need a little help with dynamic named range.

  1. #1
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.
    Last edited by Polymorpher; 08-05-2014 at 05:30 AM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

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

    little help provided

    http://www.excelforum.com/the-water-...-question.html
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,827

    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. #4
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    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. #6
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,827

    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. #7
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.
    Last edited by Polymorpher; 08-02-2014 at 08:57 AM.

  8. #8
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.

    First/Start address is
    Option 1:
    =ADDRESS(MIN(IF(B10:B19="keyword",ROW(B10:B19))),MIN(IF(B10:B19="keyword",COLUMN(B10:B19))))
    Option 2:
    =CELL("Address",INDEX(B1:B999,MATCH("keyword",B1:B999,0)+0))
    Option 3:
    =ADDRESS(MATCH("keyword",B:B,0),3,1,1)


    Second/End address is:
    Option 1:
    =ADDRESS(MAX(IF(B10:B19="keyword",ROW(B10:B19))),MAX(IF(B10:B19="keyword",COLUMN(B10:B19)))) ........ (with CTRL+SHIFT+ENTER)
    Option 2:
    =ADDRESS((COUNTIF(B10:B19,"keyword")+((-1)+COUNTIF(B10:B19,keyword))),2,1,1)
    Option 3:
    =ADDRESS((MATCH("keyword",B:B,0)+COUNTIF(B:B,"keyword")-1),3,1,1)


    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.
    Last edited by Polymorpher; 08-04-2014 at 09:04 AM.

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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))
    Last edited by Polymorpher; 08-04-2014 at 06:06 AM.

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Need a little help with dynamic named range.

    i dont think you can use non contiguous ranges like that

  12. #12
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,543

    Re: Need a little help with dynamic named range.

    Quote Originally Posted by martindwilson View Post
    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  13. #13
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.
    Last edited by Polymorpher; 08-04-2014 at 06:32 AM.

  14. #14
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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. #15
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,543

    Re: Need a little help with dynamic named range.

    Quote Originally Posted by martindwilson View Post
    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. #16
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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. #17
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,543

    Re: Need a little help with dynamic named range.

    Quote Originally Posted by Polymorpher View Post
    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. #18
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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


    Anyway - Re-download this I updated it.
    Attached Files Attached Files
    Last edited by Polymorpher; 08-04-2014 at 07:37 AM.

  19. #19
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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. #20
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,543

    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. #21
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.
    Last edited by Polymorpher; 08-04-2014 at 08:21 AM.

  22. #22
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,543

    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. #23
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.
    The same way your formula made a range out of "dog".

    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
    Last edited by Polymorpher; 08-04-2014 at 09:00 AM.

  24. #24
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Need a little help with dynamic named range.

    still not sure but maybe
    Attached Files Attached Files

  25. #25
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,543

    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. #26
    Forum Contributor
    Join Date
    08-17-2010
    Location
    London
    MS-Off Ver
    Excel 2013
    Posts
    127

    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.
    Last edited by Polymorpher; 08-04-2014 at 10:17 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 06-29-2013, 11:58 PM
  2. vlookup? match? index? MULTIPLE criteria for vlookup search problem....
    By aborg88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-11-2013, 09:56 AM
  3. [SOLVED] Search in Table vlookup / Hlookup or INDEX - MATCH command
    By dalaie in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-13-2012, 07:42 AM
  4. Replies: 1
    Last Post: 07-13-2011, 09:22 AM
  5. Using Search with either vlookup or match and index
    By jlowenstein in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-06-2005, 03:05 PM

Bookmarks

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