Dependent Drop Down Selections on different Sheets Within the Same Workbook

[Rhaiynebow]

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!

[JBeaucaire]

It appears you're using a sheetname in your reference with no need. I believe the INDIRECT() syntax would simply be:

=INDIRECT(SUBSTITUTE(A1," ",""))

This assumes you've selected something like "Regional East" in cell A1. The formula would convert that to RegionalEast and then use that as a named range reference in the INDIRECT() function.

[Rhaiynebow]

Thanks! I tried that and now I'm getting an error that states "The Source currently evaluates to an error. Do you want to continue?". I select yes, and it's not working. I do have "Regional East" selected on the "Cover Sheet" tab in cell A1.

[Haseeb Avarakkan]

define a name for Cover Sheet!A1, like CurrSelection, then use like,

=INDIRECT(SUBSTITUTE(CurrSelection," ",""))

[Rhaiynebow]

I actually did try that earlier today and I got the same error as when I used =INDIRECT(SUBSTITUTE(A1," ","")). I named that cell (Cover Sheet!A1) "Region". So in the data validation source box, I had typed =INDIRECT(SUBSTITUTE(Region," ","")).

I just realized that maybe I should have been putting that into the actual cell formula bar instead of under the DV source, so I gave that a try. It kinda works in that it does return the first list option that I have defined for "RegionalEast" which in this case is "Connecticut" with no drop down functionality. Am I doing something wrong? Thank you all so much for the speedy replies!

[JBeaucaire]

Time to post the workbook so we can see what the problem is. Point out the cells we're working on if it isn't glaringly obvious. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

[Rhaiynebow]

Thank you all! Hopefully I attached the spreadsheet correctly. I didn't highlight the areas in question, but will if I need to. Just let me know if you can't determine which cell I'm having trouble with. Oh, and Happy New Year!

*Note: Due to user error I'll need to repost the workbook in an hour or so. Please bear with me while I adjust some data. Thanks!

[Rhaiynebow]

^Bump to top since I added the updated workbook^

[Haseeb Avarakkan]

in the INDIRECT need to reference the currently selected cell, which is in 'Cover Sheet'!A1.

See the attached.

[Rhaiynebow]

Thank you SOOOOOOO much!! For reference, here's the data that Haseeb used in the Data Validation source box: =INDIRECT(SUBSTITUTE(CurrSelection," ",""))...Now I get to see if I can do it myself on another tab