Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Hi All
    I need to format a Region sheet which will use a data-validation drop-down box for the user to select an address. The addresses are on a second worksheet [Address] and the Region data-validation drop-down is populated by the "range" of addresses on the Address sheet [see attached].

    Is there a way to look up the ID on the Region sheet and return the range of addresses in the drop-down box?

    Looked through the other postings but could not find this particular need. Any help fully appreciated; will save hours [days] of work!

    Meleia
    Attached Files Attached Files

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    While clicked into cell A2 on the Region sheet, create the following named range formula
    =INDIRECT(ADDRESS(MATCH(Region!$A2,Address!$A:$A,0 ),2,,,"Address")&":"&ADDRESS(MATCH(Region!$A2,Addr ess!$A:$A,0)+COUNTIF(Address!$A:$A,Region!$A2)-1,2))

    Reference the named formula, like you did the other range names in the Data Validation.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thanks. I am not sure how this would work. I would like the formula in Region B2 to access the ID in region A2, then look up all of the addresses in Address B2 that had the matching ID and present them in a drop-down list for user selection.

    I am trying to get away from the range names as they would have to be changed in the formula with each ID change. That is what the original drop-down is doing now and it causes a lot of manual work, changing the range name in the validation box. Unless I'm missing something?
    Thanks

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by Meleia View Post
    Thanks. I am not sure how this would work. I would like the formula in Region B2 to access the ID in region A2, then look up all of the addresses in Address B2 that had the matching ID and present them in a drop-down list for user selection.

    I am trying to get away from the range names as they would have to be changed in the formula with each ID change. That is what the original drop-down is doing now and it causes a lot of manual work, changing the range name in the validation box. Unless I'm missing something?
    Thanks
    Yes, this will avoid having to create multiple range names.
    What you are doing is creating a formula that will pull the range of addresses appropriate the the ID in question. Now your Addresses & ID's will need to be sorted so that all addresses with the same ID are grouped together (but you are doing this already with your range names).
    You will be using a formula to lookup the ID and then reference the range of cells in the address sheet with addresses with the matching ID.
    The formula you are creating will be stored as a Named Range. Then you will reference the named range in your Data Validation in the same way you are referencing Named Ranges that refer to cell references right now.
    You can create formulas as Named Ranges, the real "trick" of the whole process is that you should have cell A2 selected when you build the formula.
    I am attaching a sample file. I can break the formula down and explain it if you desire.[attachment=90822:NamedRange Formula using Indirect-Address-Match_countif.xlsx]
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    This is great! I can't thank you enough. And, yes, can you break the formula down and explain the steps? I think I understand it, but will be applying it to many sheets and want to make sure I can do my own error-checking. Any further illustration you wish to add will be greatly appreciated.

    Meleia

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You can simplify a bit to:
    =OFFSET(Address!$B$1,MATCH(Region!$A2,Address!$A:$ A,0)-1,0,COUNTIF(Address!$A:$A,Region!$A2),1)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Just wanted to add a big thank you to Catherine and Rory for their replies. I've used Catherine's response and my spreadsheet manual processing time has dropped from about two hours per sheet to about 10 minutes! Thanks again!

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    I'll start by breaking down my formula (Rory's is definitely more compact )
    =INDIRECT(ADDRESS(MATCH(Region!$A2,Address!$A:$A,0 ),2,,,"Address") &":"&ADDRESS(MATCH(Region!$A2,Address!$A:$A,0)+COU NTIF(Address!$A:$A,Region!$A2)-1,2))
    The formula uses the following functions: INDIRECT, ADDRESS, MATCH, and COUNTIF. Oh and concatenation using the & symbol


    The MATCH function finds the position of an item in an array (range).
    In this case, it looks at the ID number and finds its first occurrence in Column A in the address sheet. This is the starting row for the range.
    The COUNTIF function counts how many times an item occurs within a range.
    In this case, it looks for the ID number and counts how often it occurs within Column A in the address sheet. After subtracting 1 from this number, this will be the ending row for the range.
    The ADDRESS function converts row and column numbers into a cell address using the syntax: (row number, column number, absolute/relative, reference style, Sheet Name)

    So this portion of the formula:
    (ADDRESS(MATCH(Region!$A2,Address!$A:$A,0),2,,,"Address")
    evaluates as follows:
    ADDRESS(2,2,,,"Address") = Address!$B$2 or the start of the range
    The second part of the formula:
    ADDRESS(MATCH(Region!$A2,Address!$A:$A,0)+COUNTIF(Address!$A:$A,Region!$A2)-1,2))
    evaluates as follows:
    ADDRESS(2+2-1,2) = $B$3 or the end of the range

    The two addresses are glued together using the concatenation symbol and the colon is added within double quotes - this will indicate a range.
    Address!$B$2&":"&$B$3 resulting in the text string "Address!$B$2:$B$3"
    Now this text string needs to be converted to a cell reference, and this is what the INDIRECT function does - reads Address!$B$2&":"&$B$3 as a cell reference, rather than text.

    Rory's use of the OFFSET function, uses Address,Match and Countif as well. But since the address is never written as a string, it doesn't need to be converted.
    Offset returns a "block" or range of cells from a specified starting point, using the following syntax:
    OFFSET (starting point, rows from starting point, columns from starting point, height of range returned,width of range returned)

    =OFFSET(Address!$B$1,MATCH(Region!$A2,Address!$A:$A,0)-1,0,COUNTIF(Address!$A:$A,Region!$A2),1)
    or
    =OFFSET(Address!$B$1,2-1,0,2,1)
    or from cell $B$1 on the address sheet, move down 1 row, remaining in the same column , (=Address!$B$2) return a block sized 2 rows by 1 column (=Address!$B$2:$B$3)

    While Rory's is more compact, my formula has the advantage that you can actually work it out step by step.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Star Lounger
    Join Date
    Nov 2008
    Location
    Northeast US
    Posts
    81
    Thanks
    10
    Thanked 1 Time in 1 Post
    Thank you, Catherine, for all of the time you spent on this. I really appreciate the formula breakdown -- it's how we learn best!
    Meleia

Posting Permissions

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