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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
262
Peter_SSs
Peter_SSs
J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
672
juliecooper255
J
N
  • Question Question
Data Validation using a SPILL range
Replies
3
Views
370
Nicole Jones
N
drom
  • Question Question
SUMPRODUCT to count dependent validation list column with wrong items
Replies
1
Views
263
drom
drom
S
  • Question Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
940
Domenic
D

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

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