In a List only show certain items

[gheyman]

I have two columns, the first is a list of names and the second states if that person is the "Parent" or the "Child" (Columns A&B starting in Row 5)

On a separate tab I want to have a list box where the user can select the name to be entered into that field. But I want the list to only show names of the Children "Child".

Is this possible?

 

[Eric W]

You can use a helper column to accomplish that like this:

	A	B	C	D	E	F	G
Name	Type	List	Select
Amy	Parent	Bob
Bob	Child	Cyndi
Cyndi	Child	Doug
Doug	Child	Gayle
Elayne	Parent
Frank	Parent
Gayle	Child

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10="Child",ROW($B$2:$B$10)-ROW($B$2)+1),ROWS($E$2:$E2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Your list is in A:B. Then I created a formula to pull out the "child" names in column E. Put the formula in E2, confirm with Control+Shift+Enter, then drag down the column. Finally, in G2 I created a Data Validation rule. Click on the cell, on the Data tab click Data Validation, click List in the Allow box, and put this formula in the Formula box:

=OFFSET($E$2,0,0,SUM(IF($E$2:$E$10<>"",1)))

Change the ranges in both formulas to match your sheet. Let us know if this is what you're looking for.

 

[HackSlash]

---

 

[gheyman]

Thank you!!!!

 

[Eric W]

Glad to help!