Dependent Drop Down Selections on different Sheets Within the Same Workbook

Hi - I'm new to this forum and have been searching the web all day today to try and find the answer to my question. I'm really hoping that someone here can help me out or provide some good links to reference. I'm working with Excel 2007, but have to save the file in Excel 97-2003 format as that's the version that most of my coworkers have. My scenario is this:

On Sheet 1 named "Cover Sheet" in cell A1, I have a drop down box that allows for selection of "National", "Regional East", and "Regional West". That drop down is populated by a list that I created in another sheet named "Do Not Use". The list is named "Region".

What I'm trying to do is, on a third sheet, named "Status of Risk", make a drop down in cell A4 that displays a list of the states in each region based upon what was selected on the "Cover Sheet" as the region. So if they select "National" on the "Cover Sheet", then all US states would display in the drop down in cell A4 on the "Status of Risk" sheet. If "Regional East" is selected, then only the states for that region would populate.

I have already created named lists ("RegionalEast", "RegionalWest" and "National") on the "Do Not Use" sheet with the appropriate states.

I tried using something in data validation like =INDIRECT(SUBSTITUTE("Cover Sheet!A1"," ","")), but I get the error that says you can't link to another workbook or worksheet using data validation.

I'm definitely NOT an Excel guru, so it may be that I need to get in to what I call some "hairy" functions like VLOOKUP or INDEX, but I've never used those before, so if that's the case, a detailed response would be EXTREMELY appreciated Thank you in advance!