Creating a list using w/ a function
Try this array** formula. Entered on Sheet2 cell B3:
=IF(ROWS($1:1)<=COUNTIF(Sheet1!A$1:A$5,A$3),SMALL( IF(Sheet1!A$1:A$5=A$3,Sheet1!B$1:B$5),ROWS($1:1)), "")
Copy down until you get blanks
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
Or, try this non-array version (normally entered):
=IF(ROWS($1:1)<=COUNTIF(Sheet1!A$1:A$5,A$3),LARGE( INDEX((Sheet1!A$1:A$5=A$3)*Sheet1!B$1:B$5,,1),COUN TIF(Sheet1!A$1:A$5,A$3)-(ROWS($1:1)-1)),"")
The array version calculates slightly faster on average.
Biff
"Otto" wrote in message
...
Hello,
I need help in how I can create a list by using a function. Here is what I
am trying to accomplish:
In sheet1 I have the following data
A B
1 House A 100
2 House B 500
3 House A 150
4 House C 200
5 House B 300
In sheet 2 in cell a3 I have created a drop list bottom of column A of
sheet1 where I can choose any house. Everything ok until here. Where I
need
help is how can I list the values of column B in different cells of sheet
2.
Example
Cell a3 House A
Then I would like to list all the all values of House A in sheet 1
100
150
Thanks for any help given.
|