Multiple Column Validation List

[akamax_power]

I'm having trouble finding a way to do this. Maybe I'm just retarded or it just can't be done.

I want to create a validation list that shows records from 2 columns (Col A - JobNumber, Col B - Description) but when a record is selected only the value from the first column (Col A - JobNumber) is entered. The problem is I can't do this in VBA, it needs to be in a worksheet function.

So the following code would give me a dynamic list based on the data in column A:

=OFFSET(JobList!$A$2,0,0,COUNTA(JobList!$A:$A),1)

I want a dynamic list that displays data in Column A and Column B but when selected from the list only the data from Column A is entered. Can this be done?

Hope this makes sense,


Gerald

[p45cal]

I would have said create a dynamic named range then use an activex combobox instead, ,set its:
ListFillRange to the named range
BoundColumn to 1 (the default)
ColumnCount to 2
LinkedCell to which ever cell you want,

only on trying this, the dynamic named range updates as shown by F5 and typing in the named range, but the combobox drop down does NOT adjust.
The work around involves a macro to reset the ListFillRange, but you say you can't use them.

[akamax_power]

Thanks for that option, not so sure that would work though. I'm guessing there's no easy formula to do this than.

Gerald

[mikerickson]

You can't do this with Validation. In a Validation list cell, what you see in the list, is what is put into the cell.

What you could do is
1) make a helper column next to the job number/desicription column with a formula like =A1&"-"&"B1

2)Use that column to feed the Validation list for AA1.

3)Use formulas to split the two halves of AA1 to cells AB1 and AC1