If and vlookup in Data Validation

Copper Contributor

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
YearQuartercode numberColumn1   codenumberd
2020Oct-Dec17if code number 17 selected from drop down it should show drop down of mm, mc else vlookup   11mc
       12mc
       13mm
       14mm
       15mm
       16mm
       17 

 

 

13 Replies

@Nagaraj007 

 

Any one could help me, its very urgent

@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.

Hi @Riny_van_Eekelen ,

 

Please find the below file link.

Dummy Data 

 

 

@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.

 

 

 

@Nagaraj007 Perhaps, these snapshots exemplify your objective, as hinted by @Riny_van_Eekelen 

Twifoo_0-1607717380862.png

Twifoo_1-1607717499889.png

Twifoo_2-1607717573527.png

 

 

@Twifoo 

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?

I suggest you find a rule when to choose "mc" of "mm", if the code is 17. Thereafter, you'll need a lookup formula rather than a drop-down list.

@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.

@Twifoo 

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.

@Nagaraj007 

 

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.

@JMB17 

 

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.

@JMB17 @Twifoo 

 

Hello All,

 

Could anyone please help me?

 

Regards

Nagaraj

@Nagaraj007 

 

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.