Data Validation - List not working

[wangh3]

So I have a cascading dropdown using data validation where I go from a Region_List to District_List. After selecting a Region, the "District" should contain selections only correlating to the selected Region. It looks like when I paste the "district_list2" formula into the excel sheet, I get the correct list of Districts per selected Region. However when I use the drop-down in the cell for the data validation, it's just showing my Defined Name "district_list2" in the cell instead of "Southeast; Texas; NYC". Please let me know if a screenshot would help!

 

[RoryA]

A screenshot (or description) of the actual data validation formula would help.

 

[wangh3]

district_list2 = OFFSET(INDEX(district_table,1,col_num),0,0,COUNTA(entire_col))
district_table = {SOUTHEAST; TEXAS; NYC} based off SELECTION of SNA SR EAST

 

[RoryA]

I meant a shot of the DV dialog, so I can double-check what you put there. You appear to be using dynamic ranges, which wouldn't work with INDIRECT anyway, but I'm wondering if you even have INDIRECT in your formula.

 

[wangh3]

No INDIRECT in the formulas. So it's strange that when I input the district_list2 formula into a cell, it populates correctly with the full list based off region selection. But when I put it into the data validation list, it won't pull from the list, it only shows district_list2.

 

[RoryA]

You're missing an = sign at the start of that.

 

[wangh3]

This is what I get when I input district_list2 into a cell

 

[wangh3]

WOW, THANK YOU SO MUCH! The = sign is what I was missing. This has been haunting me. Kudos!

 

[RoryA]

Glad we could help.