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, 9 views)

I opened my attachment and there was no data validation! I don't know why.
Hopefully the attached, where I've re-instated the data validation, works now. It was done on a machine with Excel 2003.

Attached Files

MSOfficeForums38742example.xls (26.5 KB, 12 views)