• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation List Question

S

Serene

Member
Hi,


I would like to create a drop down list picking data from another sheet in the same workbook. In this field, I only need a numerical value -"1,2,3,4,5" which is needed as a vlookup reference. However, in the drop down list, I would like to display as "1 - General Manager, 2 - Manager, etc.." This is to help the end user to understand what each code stands for.


How can I achieve this?


Thanks in advance!

Serene
 
Can you just create a list just like that? Or are you saying the pulldown menu is dependent upon a vlookup?
 
Hi Serene ,


What you can do is continue to use your drop-down list as it is viz. 1 - General Manager , 2 - Manager , and so on.


In your VLOOKUP formula , you can use something like :


=VLOOKUP(LEFT(Drop-Down List Value,1),Lookup Table,Column #,FALSE)


where the Drop-Down List Value will be the full string , such as "2-Manager".


Narayan
 
Dear Narayan,


Thank you for your suggestion.

I managed to use 1 - General Manager, 2 - Manager, etc in the drop down list, then in the vlookup, I use the truncate function to strip take only the numeric value. For eg,

=VLOOKUP(C2,A10:H14,LEFT(B2)+3)


Cheers

Serene
 
Dear Narayan,


Thank you for your suggestion.

I managed to use 1 - General Manager, 2 - Manager, etc in the drop down list, then in the vlookup, I use the truncate function to strip take only the numeric value. For eg,

=VLOOKUP(C2,A10:H14,LEFT(B2)+3)


Cheers

Serene
 
You must log in or register to reply here.
Back
Top