Results 1 to 4 of 4

Thread: User Interaction with options

  1. #1

    User Interaction with options



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I have two worksheets in a spreadsheet, I have a full address list in sheet 1, in sheet 2, is there any possible way to have data validation by where if the user clicks on an option then it will pull through all the data relating to that specific address?

    E.g in my data validation list, the user clicks on "Home improvements", then in the rows beneath it, it will pull through all of the address relating to "Home Improvements" from sheet 1. Is this possible?

    Thank You

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010

    Smile Re: User Interaction with Options

    Hello happy_smiler1
    You should be able to do this with an array formula like this:

    {=IFERROR(INDEX(B$19:B$99,SMALL(IF($B$19:$B$99=$J$18,ROW(B$19:B$99)-18),ROW(B1))),"")}
    In case your not familiar with these, you tell Excel by pressing Ctrl+Shift+Enter when you have completed it. Excel then places braces around the formula to indicate its array status.
    You cannot type in the braces and they are removed if you edit the formula, so you need to press C+S + Enter again after you have edited.
    In this case what you get something like a database search, that can find every instance of a search value you specify in a separate table and return other details from the matching entry.
    It can work with a drop down list (in cell J18) as in my example.

    HTH
    Hercules
    Attached Files Attached Files

  3. #3
    Hi Hercules,

    Absolutely sport on! Exactly what I was looking for.

    Many Thanks

  4. #4
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    773
    Articles
    0
    Excel Version
    2010
    Glad to be of help

Posting Permissions

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