Data Validation - List not working

wangh3

Board Regular
Joined
Jun 24, 2015
Messages
58
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!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,035
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
A screenshot (or description) of the actual data validation formula would help.
 

wangh3

Board Regular
Joined
Jun 24, 2015
Messages
58
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
 

Attachments

  • stores.PNG
    stores.PNG
    8 KB · Views: 5

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,035
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Jun 24, 2015
Messages
58

ADVERTISEMENT

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.
 

Attachments

  • dvdiag.PNG
    dvdiag.PNG
    11.8 KB · Views: 5

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,035
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You're missing an = sign at the start of that.
 
Solution

wangh3

Board Regular
Joined
Jun 24, 2015
Messages
58

ADVERTISEMENT

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

Attachments

  • districtlist2.PNG
    districtlist2.PNG
    10.3 KB · Views: 3

wangh3

Board Regular
Joined
Jun 24, 2015
Messages
58
WOW, THANK YOU SO MUCH! The = sign is what I was missing. This has been haunting me. Kudos!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,035
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad we could help. :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,587
Messages
5,765,300
Members
425,271
Latest member
kristyfinn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top