Data Valuation to select lane numbers for zones

[svendiamond]

Hi! You guys have been extremely helpful as with every problem I come to in Excel, I google it and usually find the answer here. So thanks! This one I couldn't find by Googling.

I am trying to create a list where the user will select which "Lanes" to assign to "Zones" -- for example... Zone 1 will be Lanes 1 through 10. Zone 2 will be Lanes 11 through 25. Etc, etc... I want to make it very easy for a user to change which lanes are assigned to which zone. I thought about using data valuation but I need Zone 2 to automatically bump up the *start* lane if you change the end of Zone 1.

Example: Zone 1 is set at Lanes 1-10, but I want to extend it to Lane 12. How can I make it so that Zone 2 automatically starts at Lane 13 after I set Zone 1? I would like to use drop-down menus for this because it seems like that would be the most user-friendly. Any help you can provide would be greatly appreciated! Thanks again!

 

[Snakehips]

svendiamond,

Welcome to MrExcel.

Can you clarify a few things?

Start lane and end lane are numbers entered into separate cells for each of Zone 1 and Zone 2?

Zone 1 always starts 1 ?

Top limit for Zone 1 ?

Zone 2 always last lane of Zone 1 +1 "

Top limit for Zone 2

You could have a formula in Zone 2 start that automatically sets to Zone 1 end +1 ? But that would preclude manual entry in that cell.
You could have a data validation list that adjusts so that the list contains only that number?

You could use vba?

 

[pleeseemailme]

Howdy,

How I read this is you have each option, Zones and Lanes, in columns next to each other. And you want a drop down menu so that if a person selection Zone 1 in column 'A', they only have options to select from Lanes 1-10 in column 'B'. However, if they change their minds and afterwards select Zone 2 you want the incorrect lane to be cleared from column 'B' and replaced with the first correct value for Zone 2; - but they still need to be able to select from the appropriate options for Zone 2, yes?

Are you familiar with dynamic named ranges? If so I would set up a table like the one below and set up a dynamic range that is tied to the cells in column 'B'. Have the range offset 1 column over from the appropriate zone name below. If they choose zone 1, then only Lanes 1-10 will be available to them. Then, set this named range as the list source for your data validation in column 'B'

For column 'A', if you have many zones, you could set up a pivot table to summarize all the zones from the master list into one short list. Then, you make another dynamic range for this summarized list (in case you add or remove zones). This named range will be your list source for column 'A' data validation.

I hope all this makes sense thus far.

Regarding the issue of automatically changing the values of the zones and lanes - you could put a formula in the cells in column 'B', but once a user selects a different lane from the drop-down the formula would be over written. You could get around this with using the Worksheet_Change event in VBA - will your users have macros enabled?

I hope this helps, or at least in on the right track.

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]Zone 1[/TD]
[TD="width: 64"]Lane 1[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 2[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 3[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 4[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 5[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 6[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 7[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 8[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 9[/TD]
[/TR]
[TR]
[TD]Zone 1[/TD]
[TD]Lane 10[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 11[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 12[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 13[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 14[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 15[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 16[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 17[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 18[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 19[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 20[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 21[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 22[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 23[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 24[/TD]
[/TR]
[TR]
[TD]Zone 2[/TD]
[TD]Lane 25[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

With your Data as per "Pleaseemailme's" Post (Thank You), Set in columns "AA & AB".
Click "Alt+F11" to open Vb Window.
Paste code below into window
Close Vbwindow.

To Run Code :-
Click "A1":- "Zones" Drop down Appears in "A1".
Select from "A1" Drop down:- Drop down "Lane" appears in "B1"

Option Explicit
[COLOR=Navy]Dim[/COLOR] Dic [COLOR=Navy]As[/COLOR] Object
Private [COLOR=Navy]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR=Navy]As[/COLOR] Range)
[COLOR=Navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=Navy]Then[/COLOR]
Call DDfill
    [COLOR=Navy]With[/COLOR] Target.Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
    [COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Private [COLOR=Navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=Navy]As[/COLOR] Range)
[COLOR=Navy]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR=Navy]Then[/COLOR]
    Call DDfill
[COLOR=Navy]With[/COLOR] Target.Offset(, 1).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Dic.Item(Target.Value)
[COLOR=Navy]End[/COLOR] With
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
[COLOR=Navy]Sub[/COLOR] DDfill()
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
    [COLOR=Navy]Set[/COLOR] Rng = Range(Range("AA1"), Range("AA" & Rows.Count).End(xlUp))
        [COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        Dic.Add Dn.Value, Dn.Offset(, 1)
    [COLOR=Navy]Else[/COLOR]
        Dic.Item(Dn.Value) = Dic.Item(Dn.Value) & "," & Dn.Offset(, 1)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[svendiamond]

Thank you for the quick response.

Here's a screenshot of what I'm basically trying to do. You were definitely on the right track. I want the user to be able to select the zones and have column A update based off that information. Updating column A based on G/H is the easy part though. The part I need help with is making the next Zone update based off selections in the first zone. If I select a different "end" to a Zone, I want the next Zone to recognize that and update it's *start* Lane. I'm hoping I don't have to use VBA but if necessary that's fine.

Yes I am somewhat familiar with Dynamic Ranges. The user will not be selecting Zones... only the Start Lane and End Lane of each Zone. Macros and VBA are fine if it makes it easier. The user will have Macros enabled and I usually just create simple buttons for them to use.

Thanks for any help you can provide!

 

[Snakehips]

You illustrate Zone 4 End etc.. is there a limit to the zones? Is there a limit lanes?

 

[svendiamond]

You illustrate Zone 4 End etc.. is there a limit to the zones? Is there a limit lanes?

The lanes go from 1 to 106. Currently I have it divided into 15 zones but that can change. Like if they choose lane 106 as the end of Zone 14... there would be no Zone 15. I've had it divided up to 17 zones before.

 

[Snakehips]

svendiamond,

I have tried to avoid vba for you but I'm posting this with reservations.

If your data is laid out pretty much as your screenshot then this might be getting there but it relies on there being some data input discipline. A vba solution would require same but would probably be easier to flag up any issues.

Assumptions......

Column B pre-populated with lane numbers 1 to 106
Formula as per A2 below to be copied down to row 107
Zone settings start in row 7
Zone 1 Start etc has 'Zone' and either 'Start' or 'End' separated by a strict space number space e.g. ' 1 ' ' 12 '
Formula in H9 can be dragged down as far as row of Zone 17 End
***Then clear the contents i.e. the formulas from the 'End' Rows.

In practice you would need to protect the formula cells so that the user cannot override the formula!!
Zone 1 Start could perhaps be protected at value 1 ???
Lanes 1 to 106 will always be fully allocated.

Any one Zone End lane will always be set greater than it's Start
Any Starts or Ends beyond the point where any Zone End is 106 will be blank.

During the data input or more likely, data removal if reducing Zones or altering Ends in the middle Zones then you could get a newly generated Start number that is larger than the End number below.!! Obviously the user must recognise this and then edit the other End values.

I do hope that the above makes some sort of sense and that my understanding of what you are doing is not too far off the mark!!??

Excel 2007
	A	B	C	D	E	F	G	H
1	Zone	Lane Order
2	Zone 1	1
3	Zone 1	2
4	Zone 1	3
5	Zone 2	4
6	Zone 2	5					Zone Settings
7	Zone 2	6					Zone 1 Start	1
8	Zone 2	7					Zone 1 End	3
9	Zone 3	8					Zone 2 Start	4
10	Zone 3	9					Zone 2 End	7
11	Zone 3	10					Zone 3 Start	8
12	Zone 4	11					Zone 3 End	10
13	Zone 4	12					Zone 4 Start	11
14	Zone 4	13					Zone 4 End	106
15	Zone 4	14					Zone 5 Start
16	Zone 4	15					Zone 5 End
17	Zone 4	16					Zone 6 Start
18	Zone 4	17					Zone 6 End
Sheet2

Cell Formulas
Range	Formula
A2	="Zone "& LOOKUP(B2,$H$7:$H$26,TRIM(MID($G$7:$G$26,5,3)))
H9	=IF(OR(MAX(H$7:H8)=106,H8=""),"",MAX(H$7:H8)+1)

With the values set in the example, Zone 4 goes all the way down to Lane 106.

Hope that helps or sparks an idea.

 

[svendiamond]

THANK YOU SO MUCH SNAKEHIPS! You are awesome!

 

[Snakehips]

You are welcome.
I am pleased to have sorted it without VBA. To be honest, using vba would probably have been my first instinct.

I note that the formulas I posted still refer to the the Zone settings ranges that I was testing with. I assume that you will have spotted that and extended the G and H ranges from 26 to whatever row is your last Zone End?