Data matching with data validation

Hi,

I've got a worksheet just for logging our work issues. I have a column called STATE with a drop down data validation of all the state abreviations, and a column of FORM ID also with a drop down of all the IDs. I would like to be able to choose a STATE and on the FORM ID column will show me a result of all the IDs for that particular STATE, so i can choose which ID i can use for that STATE. Is this possible? I'm a attaching a sample. Thank you!

Solution attached.

On the second tab I sorted all the entries by column A, then highlighted all values in column B where Column A is AK and gave that range the range name "AK".
Same for all other entries, so now I have ranges called AK, AL, AR, etc.

Then on tab NEEDING I entered the following in the data validation for the FormID Column in cell E3

=INDIRECT($D3)

Then I copied cell E3 and pasted special - Validation into the cells below.

Now, when I select a value from the drop down list in column D (State), Column E will display only the values that are in the named range that is showing in column D.

PROBLEM: Not all State values have a corresponding entry on the second tab, and thus there are no named ranges defined for them. In that case, the drop-down list stays empty, or, if I change the State from AK to OK, it will keep the value previously selected and any value can be typed in.

So you must make sure that there are ranges defined for EACH value in your STATE list.

Hope that helps

Teylyn

Oh my gosh!!! This is exactly what i wanted done....thank you so much! I really appreciate it. This is like an early christmas gift, i can't thank you enough!