Dependent Drop Down Menus with Dynamic Tables

[derrickjp7]

I'm trying to get a little fancy with my drop down menus and wanted to incorporate dynamic tables for my drop down lists. I'm having troubles getting them to work though. I believe I followed xelplus online tutorial to a T (

or Expandable Conditional Drop Down Lists in Excel - Xelplus - Leila Gharani) but at the last step I keep getting "The Source currently evaluates to an error. Do you want to continue?" I've tried some multiple other tutorial methods and I keep getting this same error.

In cell A2 of IOSheet sheet, I choose my controller (Controllogix, Compactlogix, Point IO or Flex IO). This cell's drop down works fine. The source for this drop down is a selection of the table headers on the DropDownList sheet. The dependent drop down lists will be found in A5:A7. The tutorial has me create "myList" in the Name Manager and use the following formula in "Refers to:"

=INDEX((TableControl[ControlLogix],TableCompact[CompactLogix],TablePoint[Point IO],TableFlex[Flex IO]),,,MATCH(IOSheet!$A$2,DropDownLists!$A$1:$D$1,0))

1|Controller|
2|___________| <--- Controller drop down menu. This seems to work fine.
3|___________|
4| IO Cards |
5|___________| <--- Dependent drop down menu
6|___________| <--- Dependent drop down menu
7|___________| <--- Dependent drop down menu

Then, I do a data validation list in cell A5 and reference myList by putting "=myList" in the source. When I hit enter, I get the error. Any suggestions what I'm doing wrong? Thanks for the help!

 

[GlennUK]

After you've made a choice in A2, does setting up the Data Validation in A5 then work correctly? I ask, as the message "The Source currently evaluates to an error. Do you want to continue?" is expected if no choice has been made ... you just continue.

 

[derrickjp7]

Wow...you're absolutely correct. I'm a little embarrassed on this one. I took it as an error on how I set it up rather than what it was actually saying. Thank you for pointing that out to me!

 

[GlennUK]

I'm glad to have helped out.