Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a list using w/ a function
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a list using w/ a function
It sounds like you want to use a dependent list.
This should help you http://www.contextures.com/xlDataVal02.html HTH, Barb Reinhardt "Otto" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Detailed List from a Summary List | Excel Worksheet Functions | |||
Creating A Breakout List from a Summary List | Excel Discussion (Misc queries) | |||
Creating a List based on your choice from Another List | Excel Discussion (Misc queries) | |||
Creating Drop Down boxes with the List function... | Excel Discussion (Misc queries) | |||
Creating a list from an existing list. | Excel Worksheet Functions |