User Interaction with options

**happy_smiler1** · 2013-03-22, 08:59 PM

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** · 2013-03-23, 07:36 PM

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