You should do a search for dynamic named ranges and data validation.
There are some files I use at:
http://normay.741.com/

Look at PO_Master_12_26_07_S.xls and RFQ_Master_05_17_07.xls.
They both have dynamic named ranges and data validation drop down cells.
Just add some parts data, unprotect the sheets and look at the named ranges and
and some of the formulas that are to the left of the data validation drop down
cells.