Dropdown list

HOw many are we taking for the drop down?  Just the 4 mentioned?  You could do that with an If/Then formula in the resultant field or a vlookup.  check this out:
http://stackoverflow.com/questions/8295816/how-to-create-an-excel-dropdown-list-that-displays-text-with-a-numeric-hidden-va

This uses lookup tables, data validation and a worksheet change event to do what you are looking for. Select an item from the data validation dropdown in the shaded area and watch it change to the number you want, instead of the text. The event only affects cells that have that specific data validation formula. Note the data validation is set not to come up with any errors either. The code is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ReEnableEvents
If Target.Validation.Formula1 = "=ItemList" Then
  Application.EnableEvents = False
  If Target <> "" Then
    Target.Value = WorksheetFunction.VLookup(Target.Value, [ItemTable], 2, False)
  End If
Else
End If
ReEnableEvents:
Application.EnableEvents = True
End Sub

Select allOpen in new window

Hope that helps
...Terry
DropDown.xlsm

Thanks so much for the code,

I must not have explained things clearly enough. You see the reason why I have the two columns in a separate worksheet is that I need to keep all that information separate. I need to use the dropdown menu in another worksheet within one of the columns.

In your example, whenever you use the dropdown menu in the column called "Item", it populates that column correctly, however it also changes the "Text" column which I won't have in  the worksheet where I'm using the dropdown.

So is there a way to just have the "Item" column populate a column in a separate worksheet?

Really appreciate your help.

Could you delete the Text column? It doesn't affect the operation of the Item column, I just put it there for demonstration purposes.

Hi, I've just tried to replicate your code. I've successfully recreated columns D, E and F, but now I need to get column A working. So Columns D, E and F (or actually they're columns A, B and C in my test document) are now in Worksheet 2. I now need to have the dropdown menu in Worksheet 1 but I'm not sure how to do that. Also do I place the VBA code in worksheet 1 or 2?

I noticed that you have the following code in your Column B:

=INDEX(ItemTable,MATCH(A4,ItemNo,0),3)

As mentioned in my previous post, I don't need this text in Worksheet 1, I only need the information that is currently in your column A, ie. the item number.

Sorry, my VBA knowledge is quite limited so I'll need a bit of direction.

Thanks again

Hi can you send a file structured how you want? Then I can update it with the data validation and the code, and I can walk you through what I did.

Sure – I've attached a sample file.

Sheet 1 just contains some columns with some header rows. Columns A, B and C in Sheet 2 show how I've replicated your columns D, E, and F. So I would need the dropdown in one of the columns in Sheet 1.

But if this is successful, I actually need 2 other dropdown lists in Sheet 1, so is it possible to extend Sheet 2, so that I have another 2 lists, for example in columns E, F and G, and also in columns I, J and K? Then 2 further dropdowns would go in another 2 columns within Sheet 1.

All the other columns in sheet 1 won't have a drop down.

I really appreciate your help.
Sample.xlsx

Here is the update, it is in Excel 2010. The step-by-step is a bit long so I put it in a sheet in the file, called Steps. So you can add as many different drop-downs as you like, just repeat the steps!
Let me know how you go
...Terry
Sample-DV.xlsm

That's fantastic! Thank you so much for spending the time on this. I'll need a day or 2 to study the setup so I'll get back to you then.

Thanks again

Hi, I've just been trying to get this to work. In point 3 of your instructions it says:

3. Add range names to lookup tables in Sheet2, using Name Manager. I used OFFSET to make them expand automatically

I've inserted the Lookup tables in sheet 2 then from the insert menu I chose "Name" and then "Define Name" (as I'm using Word for mac 2011). There's a refer to field in that dialogue where I need to select a range but what do I need to select? If I copy and paste the selection from your example, will that work?

I'm a bit confused - sorry.

Don't worry, I should be the one apologizing. I try to target my solutions to fit the person's level of experience with Excel. However, this is an unusual adaptation to achieve your requirements, so you might not have seen any of it before. Now I just set up Office 2011 in OSX so we're on the same page so to speak. I'll work through this issue in Excel 2011 and send you some screenshots and more details soon
...Terry

Great - thanks very much. Look forward to some more info.

Hi, I've managed to come a bit further with this. I worked out how to create the range names for:

ItemList1, ItemList2 and ItemList3

I also successfully created them for ItemTable1, 2 and 3.

After setting up the data validation and inserting the code into the editor, I got the dropdown menu in the first cell for each of the 3 columns and I could see the contents of the tables located in Sheet 2. There were the following problems though:

When choosing an item from the drop down, I'm not getting the item number as I wanted. Instead I'm getting both the Item Number and the Text. I checked back to your file and tested it there and strangely enough, I'm getting the same problem on your sample setup. Do you know why this is happening and whether there is a workaround?

Also, the dropdown menu is only appearing in the first cell of each of the columns. How can I repeat this for multiple rows?

Appreciate any further help.

Good work! Sorry I've been away from my test machine for a couple of days. I'll look further into this tonight. My file works as expected in Excel 2010. One reason why the number might not be appearing after you make the selection in the dropdown, is if macro security is turned on. It is turned on by default. To turn off macro security, you need to go to (in Office 2010) File > Options > Trust Center > Trust Center settings > Macro security and turn it off there. I'll find the correct location in Office 2011 tonight.

To get the dropdown menu in other cells, just copy and paste the cell with the dropdown you want there. All the data validation comes with it.
...Terry

Hi,

I looked under Excel Preferences and then clicked the "Security" button. There was a checkbox in there (that was ticked) which said "Warn before opening a file that contains macros." I unticked this but this made no difference. There doesn't seem to be any other Macro security options on a mac. Can you think of any other reason why it's not working?

Also with regard to copying and pasting the the dropdown into other cells, I can do this but I've already got cells populated and if I copy and paste then the current value of the cell gets changed to whatever was in original cell copied from. As I've already populated over 500 cells manually, is there any way to paste the drop down while keeping the current value in the cell?

Thanks for any continued advice.

Thanks a million for this!!

It's all working great now and I've easily been able to add another couple of lists without a hitch.

I really appreciate your help.

Sorry to come back to this question but could I ask just one more related question? Currently, when choosing an item from the dropdown menu, the item chosen appears in the cell. For one of the lists though, there are occasions when two items from the dropdown list need to be selected so when the first item is chosen, the number is entered into the cell, but then another item would then need to be chosen and the two numbers separated by a semi-colon, eg.

5; 13

Is it possible to do that kind of thing on a list by list basis, ie. not all lists will need this functionality. In fact currently only 1 list needs it at present.

I'm not sure if this needs to be posted as a separate question - if yes, please let me know and I will do so.

Thanks again.

Hi glad it works for you now. However the data validation only allows a single item to be selected from the list. I can't think of anything which would give you the same in-cell dropdown convenience of data validation or auto complete. There are other ways though, for example a keyboard shortcut to bring up a userform, and selection of multiple entries there places the item numbers in the cell. Would be worth starting another question referencing this one, to get input and ideas from other people
...Terry

Ok great - I'll keep that idea in mind.

Thanks again for all your help.