Creating a Dynamic List

[Ark68]

I am having a difficult time find an efficient solution to a task I would like to complete. I've managed to do a bit, but it involves an exhaustive code of nested select case and if /elseif statements. I've found it very difficult to trouble shoot as a result.

I have a database of scheduled staff and their scheduled times.

Excel 2010
	R	S	T	U	V	W
24	Shift	Crew	Name	Start	Stop
25	CUE1	1	Kelly	0.291667	0.625	25
26	CUE2	X	Not Staffed	0.291667	0.625	26
27	CUL1	2	Jen	0.666667	1	27
28	HPE1	C	Tessa	0.291667	0.625	28
29	HPE2	X	Not Staffed	0.291667	0.625	29
30	HPL1	A	Morgan	0.5625	0.895833	30
31	HPL2	X	Not Staffed	0.5625	0.895833	31
32	RPE1	C	Ashley B.	0.291667	0.625	32
33	RPE2	X	Not Staffed	0.291667	0.625	33
34	RPL1	A	Corey	0.5625	0.895833	34
35	RPL2	X	Not Staffed	0.291667	0.625	35
36	WPE1	C	Lauren	0.291667	0.625	36
37	WPE2	X	Not Staffed	0.291667	0.625	37
38	WPL1	A	Kiana	0.5625	0.895833	38
39	WPL2	A	Jessica	0.645833	0.8125	39
40	PTE1	X	Not Staffed	0.291667	0.625	40
41	PTE2	X	Not Staffed	0.291667	0.625	41
42						42
VAR_HOLD

A service range (range of time) has been defined:
lowrange = 1:30 PM, and highrange = 2:00 PM.

What I am trying to accomplish, is to generate a dynamic list of "shifts" (column R) of those employees scheduled (crew <> "X") capable of providing this service. The service start must fall between the employee's shift start and end times to be eligible.

The target range of the dynamic list (to be used as rowsource data for a combobox) is worksheet("var_hold").range("X2")

If anyone is able to assist me, I will be very grateful of the effort.

 

[jim may]

I did the below utilizing the Data Advanced Filter Option: Note there are Hidden Rows between Row 25 and 41 (which are those not qualifying per the Criteria assigned).
Hope this helps...


Excel 2010

	Q	R	S	T	U	V	W	X	Y	Z	AA
19
20										LowRange	HighRange
21										1:30 PM	2:30 PM
22
23									Crew	Start	Stop
24		Shift	Crew	Name	Start	Stop	Row#		<>X
25		CUE1	1	Kelly	0.29167	0.625	25			>=0.5625	<=0.604166666666667
27		CUL1	2	Jen	0.66667	1	27
28		HPE1	C	Tessa	0.29167	0.625	28
30		HPL1	A	Morgan	0.5625	0.89583	30
32		RPE1	C	Ashley B.	0.29167	0.625	32
34		RPL1	A	Corey	0.5625	0.89583	34
36		WPE1	C	Lauren	0.29167	0.625	36
38		WPL1	A	Kiana	0.5625	0.89583	38
39		WPL2	A	Jessica	0.64583	0.8125	39
42

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

VAR_HOLD​

Worksheet Formulas Cell Formula Y24 ="<>" & "X" Z25 =">="& $Z$21 AA25 ="<="& $AA$21 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Ark68]

Thank you Jim for your suggestion! I had overlooked the power and flexibility of the advanced filter.
I'll see what I can do to adapt it to my VBA project. I'm sure it's exactly what I need.

 

[jim may]

Glad to be of help.