Listbox with Condition

[mucah!t]

Hi all,

Is there a way to make a dynamic [as the length of the range is variable] listbox showing only the rows with "Employee:" in column B?

Excel Workbook
	B	C	D	E	F
4	Employee:	Ethan Brown
5	1-Jan	Sat	6:00 - 14:30	5:38	14:30
6	2-Jan	Sun	6:00 - 14:30	5:37	14:18
7	3-Jan	Mon	6:00 - 14:30	5:35	14:31
8	4-Jan	Tue	6:00 - 14:30	5:35	14:30
9	5-Jan	Wed	6:00 - 14:30	5:39	14:25
10	6-Jan	Thu
11	Employee:	Ethan Brown
12	1-Jan	Sat	14:00 - 22:30	13:41	22:29
13	2-Jan	Sun	14:00 - 22:30	13:40	22:16
14	3-Jan	Mon	6:00 - 14:30	5:48	14:47
15	4-Jan	Tue	14:00 - 22:30	13:42	22:26
16	Employee:	Ethan Brown
17	1-Jan	Sat
18	2-Jan	Sun	6:00 - 14:30	9:52	18:49
19	3-Jan	Mon	6:00 - 14:30	6:02	14:05
20	4-Jan	Tue
21	5-Jan	Wed
22	6-Jan	Thu	6:00 - 14:30	5:48	14:47
23	7-Jan	Fri
Sheet1

Excel 2003

 

[texasalynn]

You would need to create a dynamic named range with making another sheet that shows those lines. Here is an example I worked up using your example

Excel Workbook
	A	B	C	D
1	Match Dept	Employee:	Count	3
2	Idx	Employee:
3	4	Ethan Brown
4	11	Ethan Brown
5	16	Ethan Brown
Sheet2

Excel 2003

Cell Formulas
Range	Formula
D1	=COUNTIF(Sheet1!$B2:$B$22,B1)
B3	=IF(N($A3),INDEX(Sheet1!$C$1:$C$22,$A3),"")

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself
copy the formulas down for several rows.

Then you can use this formula to create your dynamic named range, I used Emp_List
=OFFSET(Sheet2!$B$3,0,0,MATCH("*",Sheet2!$B:$B,-1),1)