User Interaction with options

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
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
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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
 

Attachments

  • Test.xlsx
    15.2 KB · Views: 10

happy_smiler1

New member
Joined
Aug 28, 2012
Messages
67
Reaction score
0
Points
0
Excel Version(s)
office 365
Hi Hercules,

Absolutely sport on! Exactly what I was looking for.

Many Thanks
 
Top