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!

 

[svendiamond]

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?

Yes! Thanks again. I extended the ranges and all that. You gave me a very helpful jump start and I finished the rest to make it work. I've locked all the "start" lanes and used data validation for the "end" lanes where the user can pick the end lane of each zone. I made it possible to have up to 20 zones and the "A" column picks up the information perfectly.

Thanks again for your help! I didn't think it would be this easy! You got skills!