Returning information after selecting a drop down

**rick_nania** · 11-15-2007, 12:25 AM

Hello - I need some help. I am trying to create a workbook in Excel where a person can select a name from a drop list and excel will return all reports associated with that name below the name
For example: I have a worksheet with the following information:
CBL Report 1
CBL Report 2
RBL Report 1
RPL Report 3
RPL Report 1
XXL Report 1
XXL Report 2

I created a drop down list with the name CBL, XXL

Here is the process:
1) Select Name from drop down list: CBL
2) Excel should return the following reports in this format:
Report 1
Report 2
3) I select a new name such as RBL and should get:
Report 1

Should I use an IF and Vlookup statement?

Thanks

**psumvp** · 11-15-2007, 05:26 PM

Assuming your data is in sheet2 with data in the first two columns (A being the values that you are selecting from the drop down, and B being the report) and assumnig all data in A is sorted. paste the following formula below the lookup cell (which I had in cell A1 of sheet 2). You can copy and paste this formula down for more rows than your largest record has reports. It will return blanks for all extra rows.

=IF(ROW(A1)<=COUNTIF(Sheet1!A:A,Sheet2!$A$1),INDEX(Sheet1!B:B,MATCH($A$1,Sheet1!A:A,0)+ROW(A1)-1,1),"")

**Gary Brown** · 11-16-2007, 05:28 AM

You could use Microsoft Query and have 2 files (source and target). The source file would contain your information:-

CBL Report 1
CBL Report 2
RBL Report 1
RPL Report 3
RPL Report 1
XXL Report 1
XXL Report 2

The target file would have two MS Queries 1) To bring the names in (CBL,RBL,RPL, XXL) then a 2) To bring in the data, where you have a parameter, which is selected via a data validation list based on query 1.

Attached is an example (save the 2 files into a folder c:\test)

Open target.xls and on Sheet 2 select the name from cell A1, it should then return the reports for the name selected in rows 3 onwards

NOTE you will get a warning when opening the file about external queries, if your version is Excel 2002 or onwards. Select yes to run the queries