Multiple Column Lists - Different!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying desparately to set up the folowing:

I need data validation on with a dropdown list - simple enough. But, the values in the list are codes (i.e T100). T100 actually means say "Table" The description of each code is next to it in the DV list. I need to be able to click on the dropdown list and it will show both the code and the description of that code, but when one is selected it only shows the code.

I wondered about using the LEFT function to only display the first 4 chars but can't seem to get both to work at the same time. Any help would be greatly appreciated...
 
Nick,

Whatever you see in the Data Validation will, I fear, be what it puts into
the cell. I think it will require a macro (event macro that runs
automatically) to convert the two-column entry that Validation puts into the
cell into the one-value one that you want plopped into the cell. Can't be
more specific without knowing more about how your stuff is organized.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

Nick M said:
I am trying desparately to set up the folowing:

I need data validation on with a dropdown list - simple enough. But, the
Click to expand...
values in the list are codes (i.e T100). T100 actually means say "Table"
The description of each code is next to it in the DV list. I need to be
able to click on the dropdown list and it will show both the code and the
description of that code, but when one is selected it only shows the code.
I wondered about using the LEFT function to only display the first 4 chars
Click to expand...
but can't seem to get both to work at the same time. Any help would be
greatly appreciated...
 
There's a sample workbook on my web site that shows a product name and
code in the Data Validation dropdown list. After an item is selected,
the cell shows only the product name. You should be able to adapt this
to your workbook.

On the following page:

http://www.contextures.com/excelfiles.html

Under the data validation heading, find 'Data Validation "Columns"' and
download the file named DataValNameID.xls
 
Debra

Thank you very much, that's working fine except one thing. I realise from other posts that you can't increase the number of entries in the list without having to scroll, but can you increase the width of the dropdown box - currently if the item is "T100 - Table" all I can see is "T100 - Tab" for example.
 
The Data Validation dropdown is the width of the cell that it's in, to a
minimum of about 3/4".

You could use a SelectionChange event to temporarily widen the column
when it's active, then make it narrower when you select a cell in
another column. For example, with Data Validation cells in column D:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 4 Then
Columns(4).ColumnWidth = 20
Else
Columns(4).ColumnWidth = 5
End If
End Sub
 
Is there anything you don't know? Works great.

Finally, I have 3 columns with separate lists of DV, I've tried copying the Macro's for each one, it reports a duplicate name, I've tried changing the first line of the 2nd and 3rd ones (i.e Worksheet_Change, Worksheet_Change2 etc etc) but then the macros don't run on the 2nd & 3rd columns. Any last suggestion?

Thanks very much.
 
You can only have one SelectionChange sub per worksheet. The following
version will work for data validation in columns 4, 6 and 8:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Select Case Target.Column
Case 4
Columns(4).ColumnWidth = 20
Columns(6).ColumnWidth = 5
Columns(8).ColumnWidth = 5
Case 6
Columns(6).ColumnWidth = 20
Columns(4).ColumnWidth = 5
Columns(8).ColumnWidth = 5
Case 8
Columns(8).ColumnWidth = 20
Columns(4).ColumnWidth = 5
Columns(6).ColumnWidth = 5
Case Else
Columns(4).ColumnWidth = 5
Columns(6).ColumnWidth = 5
Columns(8).ColumnWidth = 5
End Select
End Sub
 
Back
Top