No Phideaus, your Excel program isn't coming undone.... that's Excel 2003.
Things improved with the newer versions, but that doesn't help you right now.

Tables weren't introduced until Excel 2007.
Excel 2003 can't apply validation from another sheet unless you use named ranges.
Manually giving each range a name and formulating when to use it... not for me.



In the attached, the Schedules is moved to a separate sheet which can be hidden.
The macro in Module1 only needed to be run once to name the ranges.
The WorkSheet_Change event monitors the cells of 'Front Sheet' column A for changes and uses the same calculation
as the NameRanges macro to determine 'on the fly' what range to use for validation in the adjacent column B cell.

Don't know if this is of any use to you or not but at least have a look.

Attached Files

Forum example_v2a.xls (45.0 KB, 8 views)

Attached an example how use dynamic ranges for depending data validation.

As you have Office2003, I tried to avoid using all newer features. When anyway there will be some problems, then let me know. At same time, I designed this example in a way, that it will be easy to redesign it to use defined Tables later (you have to use named ranges anyway, as MS didn't include the reference to Tables into Data Validation, but formulas will be easier and more automatic).

The sheet H_Lookup is meant to be hidden, but I left it visible.

Edit. I can't check it, as I haven't Office at home comp, but I'm not sure how I made the cell references for OFFSET anchor - the 1st parameter for some references must probably be absolute (like $A$1), and for some mixed (effectively like $A1)

Attached Files

DataValidationExample.xls (34.0 KB, 11 views)