Getting Data Validation to pull from TWO columns... is it possible?

[cubs610]

Hi Everyone,


I'll try and be as succinct as possible on this one. It gets a little convoluted since this is one of those things "you need to see it, to understand it". But here goes.


On one worksheet I have the following fields:

[pre]

Code	Lookup Project	SRF	Project/Description                             hours	Initials	total
1.2   Training	12	Hours, PTO Tracking, and Monthly Reporting      4.00	LM
1.2   Training	17	Update Training Records                         1.50	LM
The SRF, PROJECT, HOURS and INITIALS columns- I have to manually enter so please ignore them.


I am using this table to select from a list (Data Validation – see bottom table) to populate the Lookup Category.  Right now it is pulling from the Concatenated column.  Why? - Data>Validation>List will only allow one column to be used.  But I want both so that the user can see what the code represents that they are selecting; and then I want the selected item to populate with the CODE in one column and the PROJECT CATEGORY in another.   I tried to use the VLOOKUP function, but wasn’t able to make it work here for some reason.      Let me know what you think.

I would prefer the columns to be like this:::::

Lookup Code  Project Category                           SRF  Project/Description                              hours     Initials     total
1.2          Training                                   12   Hours, PTO Tracking, and Monthly Reporting        4.00     LM
5.11         XRF Standards - North American Region      17   Update Training Records                           1.50     LM	

DATA VALIDATION TABLE
Concatenated                                  Code     Program Category
1             Technology Administration       1.0	Technology Administration
1.1           Emergency Day                   1.1	Emergency Day
1.2           Training                        1.2	Training
1.3           Quality Activities              1.3	Quality Activities
1.4           HS&E                            1.4	HS&E
1.5           Miscellaneous Administrative    1.5	Miscellaneous Administrative

[/pre]
Sorry to be so confusing... it's the best I could do.


Thanks,

Dave

 

[Luke M]

This could get tricky, depending on how far you want to go.

Option 1)

a. Use a concatenated list like you are doing now to populate the dropdown

b1. A helper cell looks at the drop down, splits the text (LEFT/MID/RIGHT functions) to get the correct portion, puts that in correct cells.

b2. Instead of helper cells, use an event macro to convert the text into two parts. Slightly more complicated


Option 2)

a. Use a ActiveX combo box. You can set the properties to display multiple columns in dropdown, but only gives a single column as output

b. Link combo box to one cell, use a lookup to generate the 2nd column.

c. ActiveX controls can also be directly tied to VB. This could give you more options, but requires more code.

c1. Along with that, could get really fancy...whever user selects a cell in range of interest, ActiveX control "appears" and user uses that to select data. control then disappears, and it looks like a normal worksheet, similar technique to here:

http://blog.contextures.com/archives/2010/11/10/combo-box-drop-down-for-excel-worksheet/


Hope the options are clear...they are a bit different, so want to know which way you want to proceed before working on developing solution.

 

[cubs610]

@Luke M-- Thanks so much, I really appreciate it!

Best,

Dave

 

[jeffreyweir]

Or a further option would be to still have the data validation list returning an entry from the concatenated list but then using index/match to look up the row of the concatenated choice and return the associated code and description into two separate columns. Not conceptually that different to Luke's idea of using mid to split the user choice,.