View Single Post
 
Old 11-23-2010, 05:28 AM
Matthew Matthew is offline Windows XP Office 2007
Novice
 
Join Date: Nov 2010
Posts: 6
Matthew is on a distinguished road
Default

This should work (I did lift this from RagDyer in another forum)
I have attached a simple workbook so you can see it working.

You will need to chage cell references etc but thats quite easy.

Say your list is on Sheet1
Ensure that the drop down box is in A1 on sheet 2

Then in A2 of Sheet2, try this *array* formula:
=IF(COUNTIF(Sheet1!B$1:B$5,A$1)>=ROWS($1:1),INDEX( Sheet1!A$1:A$5,
SMALL(IF(Sheet1!B$1:B$5=A$1,ROW($1:$5)),ROWS($1:1) )),"")

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

*After* the CSE entry, copy down as far as you anticipate the number of
returns you'll get.
Make sure you copy down enough rows to ensure *all* possibilities are
displayed.
Attached Files
File Type: xls Multi Returns.xls (29.0 KB, 18 views)

Last edited by Matthew; 11-23-2010 at 06:59 AM. Reason: correction
Reply With Quote