Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation-Custom lookup list? (2000XL)

    Hi XL'ers

    Now that I've discovered Data Validation, I'm not having any luck with changing a list contents on the fly. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> Is it possible to use a named range list that performs a lookup first, before returning a custom list that matches the reference?

    For example, I have one master list (2 column range) that consists of 800+ report items, located on a very hidden sheet in columnB. In column A, I have an index that is extrapolated from a checklist. This checklist has 11 categories and anywhere from 4 to 30 action items. For each of these action items, there are anywhere from 1 to 20+ possible report items (1a, 1b, 1c, 1d, 1e and so on thru 11d). So for each item in colB, there is a coressponding index in col A.

    How can I have the data validation look up the value of F24 which may happen to contain the value "1d"? I want the validation list to drop from G24 and to list only the master list items that relate to "1d" and ditto if cell B100 contained "8ab", cell C100's validation would generate a list based on the value in B100.

    If Data Validation isn't up to this task, then I guess I would need to code a combo/listbox based on the value in a given activecell. In that case, how would I write the snippet so that for each instance of the value in the active cell, the row item is added to the multi column list? I am familar with creating a list, poluating the spreadsheet & sorting--I'm just stuck on creating the list based on a "selector" value.

    The idea is to keep this as lightweight as possible and have one reuseable code, instead of creating a named range for each possible combination, especially since I would expect this list to grow over time.

    Thanks

    AJF

  2. #2
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    Which is impossible? The question or the answer?

    In otherwords, if B1 is data validated, can the list evaluate the value of A1 to determine which list items will be shown, e.g. Look up value is "c*" and the return is a list of all items begining with "c". If it is "5*" then return a list begining with 5.

    Thanks

    AJF

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    << Which is impossible? The question or the answer? >>

    Probably neither. If you could post a small workbook illustrating what you are attempting to accomplish, I'm sure somebody here can help.

  4. #4
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    Ok, here is an example of what I'm trying to get. The object is to have data validation list cities based on their respective states, therefore if a user chooses Michigan, Los Angeles is not a valid nor available dropdown choice

    Thanks

    AJF
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    Hi Andy,

    Off the cuff, I think the goal is to reassign the range name for the data validation pick from list upon each instance of the cell (A2 in your sample) contents changing. Attached is a somewhat unpretty example of this in that the ranges in Sheet2 are hard coded. There may be some here who will improve on this; I would be interested in seeing a more efficient example also. Anyway, here is what I came up with...

    Also note that the list of states in Sheet 2 must be in order for this to work!
    Attached Files Attached Files

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    There is a way to change the contents of a second list depending on the choice of the first list. See my post about dynamic lists with data validation.

  7. #7
    Lounger
    Join Date
    Nov 2001
    Location
    MI, USA
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    Micheal & Hans,
    Thanks, they'll both work for me. Much appreciated

    AJF

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Impossible Data Validation(2000XL)

    Hi Mike,

    I put your code away in my bag of goodies for future use. Good stuff.

    I did have some questions.
    1. Why must the list of states be in order, per your comment? I noticed there were no cities for Alaska and Hawaii. If I add Hawaii with 2 cities at the end of the list, other than changing b148 to b150, this would seem to work. It seems the condition is that all cities for a given state need to be together.
    2. I couldn't see how the ranges State and SelectRange were being used.
    3. I know you said user beware-a few thoughts:
    a. if a match is made in the first loop, is there a way to start the second loop at the row where the match was made? Perhaps that would no longer allow a For each to be used. Perhaps it would be something like
    For i=a to 148
    'construct cell to look at based on i
    ' check on state
    next i
    This would also allow an "exit for" once matches on state stop occurring, rather than going to 148 or whatever the end is. But 148 isn't that much to search.

    4. How is Sheet2!A2 used? I see it is the first row where a match is made on the state but don't see how it's used.

    5. What if no match is found in 1st loop? Was the list of unique states set up only for those states that have a city? (Didn't check other than to notice Alaska and Hawaii missing in lists)

    Good stuff.

    Fred

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    Hi Fred,

    Even though I wrote in order, I meant to write contiguous i.e. all cities for Alaska would have to be together, all cities for Vermont would have to be together, etc.

    The range names State and SelectRange were left from the original uploaded file.

    The whole column "A" in the second sheet is superfluous. I inserted it while playing around with a solution and forgot to delete it!

    There may be a better way to construct the loops, with the second loop, I was not sure how to refer to the range in R1C1 style. Using R1C1, I think you could start the search with row "a" as you suggested.

    I was hoping one of the gurus here might come back with a three liner that did the same thing!

    Thanks,

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    If the data is laid out differently, states in column a of sheet 2, and cities relating to each state in the row containing the city, it seems there is a more efficient way of changing the range name by using the match function to return the row number of the state selected and making the name "City" refer to that row.

    HeHe...almost a 3 liner...
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Impossible Data Validation(2000XL)

    Mike,

    From another thread on finding the last filled cell (from Sammy Barrett) per the comment in your code. You might want to check the thread as a whole. I'm not sure I quite follow Sammy's code or the others but there must be something in there you can use. If nothing else, once finding the row match, you can do a loop to find the first blank col and subtract 1.

    ------
    Subject: Re: finding last filled cell (2000 sr1) [#110230]
    Poster: SammyB
    Posted on: 23-Jan-02 13:55

    Neil, here's how I do it:
    Option Explicit
    Sub Ends()
    Dim lastInColumn As Range
    Dim lastInRow As Range
    With ActiveSheet.UsedRange
    Set lastInColumn = Cells(.Row + .Rows.Count, ActiveCell.Column).End(xlUp)
    Set lastInRow = Cells(ActiveCell.Row, .Column + .Columns.Count).End(xlToLeft)
    End With
    MsgBox lastInColumn.Address & " is last in the current
    column."
    MsgBox lastInRow.Address & " is last in the current
    row."
    End Sub
    ----
    I've updated my goodie bag with your new post.

    Fred

  12. #12
    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

    Re: Impossible Data Validation(2000XL)

    Hi Mike,
    Just as a slight variation on your code, you could use something like:
    Sub DefineRng()
    Dim rngLookup As Range, rngReturn As Range
    Set rngLookup = ThisWorkbook.Sheets(1).Range("A2")
    Set rngReturn = Sheet2.UsedRange.Find(rngLookup)
    With rngReturn
    ThisWorkbook.Names.Add Name:="City", RefersTo:=Range(.Offset(0, 1), .Offset(0, Sheet2.UsedRange.Columns.Count + 1).End(xlToLeft))
    End With
    Set rngLookup = Nothing
    Set rngReturn = Nothing
    End Sub
    which is now firmly embedded in my code samples - thanks! Of course, it's more than 3 lines! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Later...
    Very ugly 3 line version:
    With Sheet2.Columns("A:A").Find(Sheet1.Range("A2"))
    ThisWorkbook.Names.Add Name:="City", RefersTo:=Range(.Offset(0, 1), .Offset(0, Sheet2.UsedRange.Columns.Count + 1).End(xlToLeft))
    End With
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Impossible Data Validation(2000XL)

    HeHe...I knew there was a three liner somewhere...surprised Geoff hasn't put a "one liner" on us...yet! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Thanks,

Posting Permissions

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