Data Validation: Combine Two Cells In Drop Down Only

[NFLnut]

I am a long time Excel user, and have enough of a grasp of creating formulas but I have to admit that I know next to nothing about VB. I found a thread which discussed what I wanted to do with Data Validation and I "applied" the Visual Basic code to the worksheet but it will not work. Here is what I am trying to do:

I have two columns that I want to appear combined in the drop down list, but once the selection is made, I only want the first column to be entered into the cell. Column B is medical procedure codes (five digit numbers) and the second column (column C) is the description of the procedure code.

Example:

B2 = 50501

C2 = X-Ray

I2 = 50501 -- X-Ray

Staff will need to see the description (in C2) to make the proper proc code selection. When they select the item in the drop down -- using the example above they would see " 50501 -- X-Ray " the cell would then be populated with only " 50501 " . I created a third (non adjacent) column that combines the two (column I) which contains this formula: =B2 & " -- " &C2 . Rows 2-32 of each column contain the actual data, and row 1 of each column contains the title/description of the data below it. I named the actual data (procedure codes) in B2-32 "ProcCode," C2-32 "Description," and I2-32 "CodeDescrip" in the name field. The cells that I will eventually place this data validation/drop down on the main worksheet (which is titled "Daysheet") into is G9:J36 (but am just trying to get it working in G9 for now).

I downloaded this formula from another site: http://www.contextures.com/DataValNameID.zip (I am using MS Excel 2003/XP) Which I adapted to the following and placed under "General" "Declarations" (it also appears under "Worksheet" "Change"):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Lookups").Range("B1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Lookups").Range("CodeDescrip"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

The columns that have the potential drop down data ("ProcCode" "Description" and "CodeDescrip") are in another worksheet entitled "Lookups". I have also tried to place "ProcCode" in place of where it says "B1."

In the cell that I want the validation (I actually have a range of cells that I will eventually put this validation into if I can get it working in one cell first) I have chosen "Data/Validation/List/" and in the source box I entered "=CodeDescrip". I have been trying to get this to work for several days with no luck. Either the VB code is not running or I have not made the proper changes to the code above. BTW -- I need to use validation versus a ComboBox because the codes will/may be replaced or added to, AND because so many cells on this sheet will contain this drop down menu that validation will make for a cleaner interface and be less confusing to the users (whom are slightly computer literate ;^) ). Also, because I know that there are ways that this "can" be done using validation and VB, I just need to figure out how to get it to work in this case.

I have also tried another method at other sites:

http://www.mrexcel.com/forum/showthread.php?t=50938 (post #4)

and

http://www.pcreview.co.uk/forums/data-validation-multiple-columns-drop-down-t2741440.html

with no luck. I am sure it is operator/programmer (ME!) error, but am wondering if some kind person could help a moron out! I actually have to adapt this throughout this Excel project so I need to figure this out. Whch opens one last question: How would I use the same VB formula in other cells (with other data from other columns) without screwing up THIS? I would love to know how to use VB and understand it better (and I will do that in the future), but for now I need to get this project done. Sorry for the length of this post but I just wanted to eliminate any unnecessary work for anyone who may be able to help.

 

[swst]

Don't know if this will help. But. If you have another column next to the one where you choose the combination description you could do a simple " LEFT(cell,5)

That would give you the proc code

Steve

 

[NFLnut]

Don't know if this will help. But. If you have another column next to the one where you choose the combination description you could do a simple " LEFT(cell,5)

That would give you the proc code

Steve

I don't completely understand .. Do you mean placing an additional column on the "Lookups" worksheet? Where would I put that code? (As I said .. I'm a VB moron).

 

[swst]

Hi
This not VB.
This a formula.

So when they choose from your validation they might choose 50501--XRay
This will sit in,say, cell I2

So in column J cell J2 enter the following formula = left(I2,5)
In cell J2 will be entered 50501

hope this helps

Steve

 

[NFLnut]

Thank you for your help, and now I understand what you are saying. I filled column J with that formula and now have a column of the proper five digit codes. However, remember that the worksheet that will have the drop-down cells is different from the worksheet that contains these codes.

The worksheet with the drop down list/cells is entitled "Daysheet" and the worksheet with all of the codes (and the "CodeDescrip" column and the new "J" column with the formula you gave me) is entitled "Lookups." So what do I enter into the data validation formula for those drop down list cells? Remember that I want the drop down list to state (e.g.) "50501 -- X-Ray" but the data entered into the cell what is now in Lookups!J2:J34. And I won't I need to somehow name that range since it will otherwise return a "cannot use data from another worksheet" error?

 

[NFLnut]

Looking at this a little more the last couple of hours .. I don't think the non-VB method is going to work. So I'm back to soliciting help as to why this VB formula in my first post (that I downloaded from Contextures.com) that I tried to adapt is not working. It "should" work as the example from that site ( DataValNameID.zip ) does exactly what I want to do. I just can't make the proper changes to get it to work on my project (probably because, as I stated, I'm a VB moron).

Anyone?

 

[siddsg]

Hello,
Here's what I did without any VBA programming:

I have data as below:
A1 = 1, B1 = apples
A2 = 2, B2 = oranges

Step 1: create another column with concatenated values:
=A1 & " -- " & B1
=A2 & " -- " & B2

Step 2: Create a dropdown using the usual method.....Data > Data Validation