Creating a list using w/ a function

[Guest]

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.

 

[Guest]

It sounds like you want to use a dependent list.

This should help you

http://www.contextures.com/xlDataVal02.html

HTH,
Barb Reinhardt

 

[T. Valko]

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),COUNTIF(Sheet1!A$1:A$5,A$3)-(ROWS($1:1)-1)),"")

The array version calculates slightly faster on average.

Biff