Okay this worked just great. I now have a similar question along this same line. Can you have a drop down list be created after you enter something in a field, without having to run code or press a button. Like in Access where you can do after update or whatever. What I'm needing is the drop down list to go off of what is entered in column A. Like someone doing data entry. So they enter data in Column A, press enter, then depending on what they entered, Column B will be the corresponding drop down list. Column A and the drop down list are on a seperate tab in the worksheet. So I look up what was entered in column A and get a set of data to create the drop down list. Make sense?
Just extend the Named Range. I.E. my example used A1:A12. Got Insert-->Name-->Define and select the name, then in the Refers To: box, just adjust the range. As you add data to the range it will automatically appear in the Validation cell.
I think I didn't make myself clear enough. Attached is a spreadsheet that on Sheet 1 I have the lists that I'm creating my drop down lists from on Sheet 2. But what I want is when I'm on sheet 2 column A, and I'm entering data. Let's say I enter in test1, I want column B to automatically create the drop down list that is associated to test1 range of cells after I hit the enter key. Look at the spreadsheet maybe that might help explain a little.