Hello Excel Experts
I'm trying to set up some drop-down menus, with the first one determining what will show in the second one. The first one (and I'll use the actual cells in my sheet so I don't get confused), located in cell E15 will show a range of numbers from 1 to 10 - defaulting to 1. The second drop down menu will only allow the user to pick a value of 0 if the first menu is showing from 1-3, a value of 0-1 if the menu is showing 4-6, 0-3 if the menu is showing 7-9, and finally a value of 0-5 if the menu is showing a 10. I hope that's somewhat clear.
After some searching around I came up with this for my Data Validation source:
=INDIRECT(VLOOKUP($E$15,SkillLookup,2,0)&"List")
The menus are on one sheet, and the formulas (for lack of a better word) are on another sheet - called Formulas. I've created ten named list of my corresponding ten possible choices from the first drop-down menu, named FirstList - TenthList inclusive. I've also created a table with the numbers one to ten listed next to the ten listed 'lists' - which I've named SkillLookUp. All of this is on the Formula sheet.
I am, of course, getting an error telling me that "The source currently evaluates to an error. Do you want to continue?" The second drop down will show nothing but a zero (which is what was typed in there before the drop down was created). I'm at a bit of a loss as to just how to go about fixing this - or really, what it is I've done wrong. I would have been more comfortable if I could have just put in a nice long, complicated, IF statement into the Data Validation box, but when I tried that it errored out too.
Thank you for any and all help.
I'm trying to set up some drop-down menus, with the first one determining what will show in the second one. The first one (and I'll use the actual cells in my sheet so I don't get confused), located in cell E15 will show a range of numbers from 1 to 10 - defaulting to 1. The second drop down menu will only allow the user to pick a value of 0 if the first menu is showing from 1-3, a value of 0-1 if the menu is showing 4-6, 0-3 if the menu is showing 7-9, and finally a value of 0-5 if the menu is showing a 10. I hope that's somewhat clear.
After some searching around I came up with this for my Data Validation source:
=INDIRECT(VLOOKUP($E$15,SkillLookup,2,0)&"List")
The menus are on one sheet, and the formulas (for lack of a better word) are on another sheet - called Formulas. I've created ten named list of my corresponding ten possible choices from the first drop-down menu, named FirstList - TenthList inclusive. I've also created a table with the numbers one to ten listed next to the ten listed 'lists' - which I've named SkillLookUp. All of this is on the Formula sheet.
I am, of course, getting an error telling me that "The source currently evaluates to an error. Do you want to continue?" The second drop down will show nothing but a zero (which is what was typed in there before the drop down was created). I'm at a bit of a loss as to just how to go about fixing this - or really, what it is I've done wrong. I would have been more comfortable if I could have just put in a nice long, complicated, IF statement into the Data Validation box, but when I tried that it errored out too.
Thank you for any and all help.
