Creating Dynamic Ranges For Data Validation

P

patrick_oneal

New Member
Joined
Nov 14, 2016
Messages
12
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.

[TABLE="width: 100"]
<tbody>[TR]
[TD]Names:[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[/TR]
[TR]
[TD]Ralphie[/TD]
[/TR]
</tbody>[/TABLE]

The Crew page will reference those cells:

[TABLE="width: 150"]
<tbody>[TR]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]Crew 1[/TD]
[/TR]
[TR]
[TD]Ralphie[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]Crew 2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Crew 3[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
patrick_oneal said:
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.

[TABLE="width: 100"]
<tbody>[TR]
[TD]Names:[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[/TR]
[TR]
[TD]Ralphie[/TD]
[/TR]
</tbody>[/TABLE]

The Crew page will reference those cells:

[TABLE="width: 150"]
<tbody>[TR]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]Crew 1[/TD]
[/TR]
[TR]
[TD]Ralphie[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]Crew 2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Crew 3[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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
Click to expand...

not clear show us some raw data please
 
Upvote 0
Here is some Raw Looks at the product:

[TABLE="width: 720"]
<tbody>[TR]
[TD="class: xl39247, width: 120, bgcolor: black"]1st
[/TD]
[TD="class: xl39247, width: 120, bgcolor: black"]2nd
[/TD]
[TD="class: xl39247, width: 120, bgcolor: black"]3rd
[/TD]
[TD="class: xl39247, width: 120, bgcolor: black"]4th
[/TD]
[TD="class: xl39247, width: 120, bgcolor: black"]5th
[/TD]
[TD="class: xl39247, width: 120, bgcolor: black"]6th
[/TD]
[TD="class: xl39247, width: 120, bgcolor: black"]7th
[/TD]
[TD="class: xl39247, width: 120, bgcolor: black"]8th
[/TD]
[/TR]
[TR]
[TD="class: xl39248, bgcolor: #BFBFBF"]0000
[/TD]
[TD="class: xl39248, bgcolor: #BFBFBF"]0300
[/TD]
[TD="class: xl39248, bgcolor: #BFBFBF"]0600
[/TD]
[TD="class: xl39248, bgcolor: #BFBFBF"]0900
[/TD]
[TD="class: xl39248, bgcolor: #BFBFBF"]1200
[/TD]
[TD="class: xl39248, bgcolor: #BFBFBF"]1500
[/TD]
[TD="class: xl39248, bgcolor: #BFBFBF"]1800
[/TD]
[TD="class: xl39248, bgcolor: #BFBFBF"]2100
[/TD]
[/TR]
[TR]
[TD="class: xl39249, bgcolor: #00B050"]Hewitt
[/TD]
[TD="class: xl39250, bgcolor: #00B050"]Hewitt
[/TD]
[TD="class: xl39249, bgcolor: #00B050"]Hewitt
[/TD]
[TD="class: xl39250, bgcolor: #00B050"]Hewitt
[/TD]
[TD="class: xl39251, bgcolor: #538DD5"]Winsett
[/TD]
[TD="class: xl39252, bgcolor: #538DD5"]Winsett
[/TD]
[TD="class: xl39253, bgcolor: #8A0000"]Divyak
[/TD]
[TD="class: xl39254, bgcolor: #8A0000"]Divyak
[/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"]McKenna
[/TD]
[TD="class: xl39256, bgcolor: #00B050"]McKenna
[/TD]
[TD="class: xl39255, bgcolor: #00B050"]McKenna
[/TD]
[TD="class: xl39256, bgcolor: #00B050"]McKenna
[/TD]
[TD="class: xl39257, bgcolor: #538DD5"]Stead
[/TD]
[TD="class: xl39258, bgcolor: #538DD5"]Stead
[/TD]
[TD="class: xl39259, bgcolor: #8A0000"]Western
[/TD]
[TD="class: xl39260, bgcolor: #8A0000"]Western
[/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"]Martiny
[/TD]
[TD="class: xl39260, bgcolor: #8A0000"]Martiny
[/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39260, bgcolor: #8A0000"][/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39260, bgcolor: #8A0000"][/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39260, bgcolor: #8A0000"][/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39260, bgcolor: #8A0000"][/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39260, bgcolor: #8A0000"][/TD]
[/TR]
[TR]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39256, bgcolor: #00B050"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39260, bgcolor: #8A0000"][/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"]Divyak
[/TD]
[TD="class: xl39261, bgcolor: #8A0000"]Divyak
[/TD]
[TD="class: xl39257, bgcolor: #538DD5"]Winsett
[/TD]
[TD="class: xl39258, bgcolor: #538DD5"]Winsett
[/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"]Digsby
[/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"]Digsby
[/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"]Digsby
[/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"]Digsby
[/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"]Western
[/TD]
[TD="class: xl39261, bgcolor: #8A0000"]Western
[/TD]
[TD="class: xl39257, bgcolor: #538DD5"]Stead
[/TD]
[TD="class: xl39258, bgcolor: #538DD5"]Stead
[/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"]Evans
[/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"]Evans
[/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"]Evans
[/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"]Evans
[/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"]Martiny
[/TD]
[TD="class: xl39261, bgcolor: #8A0000"]Martiny
[/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"][/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39261, bgcolor: #8A0000"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"]Yabsley
[/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"]Yabsley
[/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"]Yabsley
[/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"]Yabsley
[/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39261, bgcolor: #8A0000"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"][/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39261, bgcolor: #8A0000"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"][/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39261, bgcolor: #8A0000"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"][/TD]
[/TR]
[TR]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39261, bgcolor: #8A0000"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"][/TD]
[/TR]
[TR]
[TD="class: xl39265, bgcolor: #8A0000"][/TD]
[TD="class: xl39266, bgcolor: #8A0000"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39258, bgcolor: #538DD5"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39263, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39264, bgcolor: #B1A0C7"][/TD]
[/TR]
[TR]
[TD="class: xl39267, bgcolor: black"]Reserve
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39267, bgcolor: black"]Reserve
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39267, bgcolor: black"]Reserve
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39267, bgcolor: black"]Reserve
[/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"]Digsby
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"]Divyak
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"]Hewitt
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"]Winsett
[/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"]Evans
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"]Western
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"]McKenna
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"]Stead
[/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"]Martiny
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"]Yabsley
[/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39262, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39259, bgcolor: #8A0000"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39255, bgcolor: #00B050"][/TD]
[TD="class: xl39268, bgcolor: gray"][/TD]
[TD="class: xl39257, bgcolor: #538DD5"][/TD]
[TD="class: xl39269, bgcolor: gray"][/TD]
[/TR]
[TR]
[TD="class: xl39270, bgcolor: #B1A0C7"][/TD]
[TD="class: xl39271, bgcolor: gray"][/TD]
[TD="class: xl39265, bgcolor: #8A0000"][/TD]
[TD="class: xl39271, bgcolor: gray"][/TD]
[TD="class: xl39272, bgcolor: #00B050"][/TD]
[TD="class: xl39271, bgcolor: gray"][/TD]
[TD="class: xl39273, bgcolor: #538DD5"][/TD]
[TD="class: xl39274, bgcolor: gray"][/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Dynamic Data Validation List
Replies
5
Views
287
Peter_SSs
Peter_SSs
M
  • Solved
Data Validation question
Replies
4
Views
351
Peter_SSs
Peter_SSs
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
241
Peter_SSs
Peter_SSs
J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
695
juliecooper255
J
R
  • Question Question
Add to Data Validation Values From Data Entry Cell
Replies
4
Views
263
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top