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 sheet2YearQuartercode numberColumn1 codenumberd2020Oct-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

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.https://www.transfernow.net/X5TTom122020

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

If and vlookup in Data Validation | Microsoft Community Hub

13 Replies

Twifoo
Silver Contributor
Dec 11, 2020
Nagaraj007 Perhaps, these snapshots exemplify your objective, as hinted by Riny_van_Eekelen

Indirect Lists.xlsx10 KB
- Nagaraj007
  Copper Contributor
  Dec 14, 2020
  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?
  - Twifoo
    Silver Contributor
    Dec 14, 2020
    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.
Nagaraj007
Copper Contributor
Dec 09, 2020
Nagaraj007

Any one could help me, its very urgent
- Riny_van_Eekelen
  Platinum Contributor
  Dec 09, 2020
  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.
  - Nagaraj007
    Copper Contributor
    Dec 11, 2020
    Hi Riny_van_Eekelen ,
    
    Please find the below file link.
    https://www.transfernow.net/X5TTom122020

Forum Discussion

If and vlookup in Data Validation

13 Replies

Resources

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