Creating Dynamic Ranges For Data Validation

[patrick_oneal]

Hello All,

Thank you for viewing this message, I hope you can find a better solution for this issue.

Background:
I have to create a schedule that has a data validation list for a specific jobs. My spreadsheet is divided into 3 tabs. The first is a list called Crews that will break out all of my manning into cells B3-B100. I must then divide those names further into job specific crews. I have to maintain a set number of cells to account for incoming manning. I need to make a dynamic list WITHOUT VB, it's disable on my computer, that can remove those blank cells when I create a data validation.

Names:

Bob

Sarah

Frank

Ralphie

<tbody>
</tbody>

The Crew page will reference those cells:

Monday

Crew 1

Ralphie

0

Crew 2

0

Bob

Crew 3

Sarah

0

<tbody>
</tbody>

The zeros represent the cells referencing the Crew sheets: =Crews!B3
I need to find a formula that can dynamically combine those into a single validation that ignores the 0s that are false returns from the other sheet.

















kjkhl

 

[oldbrewer]

Hello All,

Thank you for viewing this message, I hope you can find a better solution for this issue.

Background:
I have to create a schedule that has a data validation list for a specific jobs. My spreadsheet is divided into 3 tabs. The first is a list called Crews that will break out all of my manning into cells B3-B100. I must then divide those names further into job specific crews. I have to maintain a set number of cells to account for incoming manning. I need to make a dynamic list WITHOUT VB, it's disable on my computer, that can remove those blank cells when I create a data validation.

Names:

Bob

Sarah

Frank

Ralphie

<tbody>
</tbody>

The Crew page will reference those cells:

Monday

Crew 1

Ralphie

0

Crew 2

0

Bob

Crew 3

Sarah

0

<tbody>
</tbody>

The zeros represent the cells referencing the Crew sheets: =Crews!B3
I need to find a formula that can dynamically combine those into a single validation that ignores the 0s that are false returns from the other sheet.

















kjkhl

not clear show us some raw data please

 

[patrick_oneal]

Here is some Raw Looks at the product:

1st	2nd	3rd	4th	5th	6th	7th	8th
0000	0300	0600	0900	1200	1500	1800	2100
Hewitt	Hewitt	Hewitt	Hewitt	Winsett	Winsett	Divyak	Divyak
McKenna	McKenna	McKenna	McKenna	Stead	Stead	Western	Western
						Martiny	Martiny
Divyak	Divyak	Winsett	Winsett	Digsby	Digsby	Digsby	Digsby
Western	Western	Stead	Stead	Evans	Evans	Evans	Evans
Martiny	Martiny
				Yabsley	Yabsley	Yabsley	Yabsley
Reserve		Reserve		Reserve		Reserve
Digsby		Divyak		Hewitt		Winsett
Evans		Western		McKenna		Stead
		Martiny
Yabsley

<tbody>
</tbody>

The colors represent shifts,
These are very simple references of =Crews!B3 to assign the member to their appropriate crews.
I need to copy from the top of each row to the bottom, and remove all of the blanks. Either via a formula, or just data validation.