Dec 08 2020 02:27 AM
Hello All,
I have a excel where in i want the users to select from dropdown only for one specific number (ex 17) apart from that any number they select it should do vlookup from sheet2.
Below is the screen snip for your reference.
Sheet 1 | sheet2 | |||||||
Year | Quarter | code number | Column1 | codenumber | d | |||
2020 | Oct-Dec | 17 | if code number 17 selected from drop down it should show drop down of mm, mc else vlookup | 11 | mc | |||
12 | mc | |||||||
13 | mm | |||||||
14 | mm | |||||||
15 | mm | |||||||
16 | mm | |||||||
17 |
Dec 08 2020 08:37 PM
Dec 08 2020 10:12 PM
@Nagaraj007 Begin by uploading your file (remove any private and confidential information), rather than cut and paste a table that doesn't seem to represent your actual file.
Dec 11 2020 03:46 AM
Dec 11 2020 05:44 AM
@Nagaraj007 Thanks! Unless I'm mistaken, you can not define a single data validation rule, returning a list for one particular value and a VLOOKUP for all others. If it can be done, I haven't yet discovered it.
In stead, you would define named ranges for the values "_11" through "_17", each listing the possible options to choose from, even though 11 to 16 would only have one option. Use INDIRECT to point to the named ranges as the source for your lists. It would look something like this: =INDIRECT("_"&C3). The underscore is needed as named ranges may not start with a number.
Dec 11 2020 12:15 PM
Dec 13 2020 10:02 PM
I got the output required as per your excel attachment.
However the problem is i need to add 171 items under name manager. Is there any workaround for this?
Dec 14 2020 09:55 AM
Dec 14 2020 09:13 PM
@Twifoo ,
Hi, If you could show me in the same excel which you provided it would be useful. As i am very new to this formulas.
Dec 15 2020 09:15 PM
Could you please help me? I am waiting as i need to add 171 name managers. If you could send me the excel with the rules that would be of great help.
Dec 15 2020 10:56 PM
I don't know if it's an option, but you could set up an input column only for Code 17 that uses data validation (Indirect function) to reference a named range of options for Code 17 (the "mc" and "mm"). So, when the input value is not 17, the data validation drop box won't work.
Then, in another cell, use an IF statement to pull the value from the drop down cell when the input =17 or vlookup for all other values.
I attached a file to illustrate.
Dec 16 2020 03:14 AM
Thanks for sharing the excel, however we have only one column and we need to get dropdown as well vlookup in one go. Please advise.
Dec 20 2020 10:35 PM
Dec 21 2020 11:32 AM
The only other thing I could suggest is to use vba to add the data validation for code 17 or perform the lookup and put the value in.