Dependent Drop Down Menus with Dynamic Tables

derrickjp7

New Member
Joined
Jan 30, 2020
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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

New Member
Joined
Jan 30, 2020
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,122
Messages
5,628,824
Members
416,342
Latest member
BlueDevil12

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