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