Validation drop-down boxes

[Tami]

i know this is a long shot but i thought i'd try asking...

I have a spread sheet that has the validation drop down box pointing to a
list in column A.

column A contains a bunch of 2 digit codes: 01, 02, 03 etc. Is there a way
that the drop down box shows that 01 is black, 02 is green, 03 is blue, etc?

 

[Gord Dibben]

After a selection is made from the DV dropdown you can color the selection
using CF.

But.........the individual entries cannot be colored within the dropdown.


Gord Dibben MS Excel MVP

 

[Tami]

i apolgize, i think my example was confusing because i used color names.
assume the list is 01-Swimwear, 02-Jackets, 03-Pants, etc.
So any other options?
Tami

 

[Gord Dibben]

You want when you select 01 that Swimwear appears in the same cell?

Select 02 and Jackets appears in the cell?

You can do that with event code and a lookup table.

Create the lookup table first.

Then create the dropdown in A1

Now insert the code below to the sheet module by right-click on the sheet
tab and "View Code".

Copy/Paste into that module................Edit to suit then Alt + q to
return to Excel.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vals As Range
Dim R As Range
Dim RR As Range

Set R = Me.Range("A1") 'DV dropdown cell
Set Vals = Me.Range("P1:Q100") 'lookup table

'column P is list range for DV dropdown
'column Q has list of return numbers
'these could be on another worksheet

'Set Vals = Sheets("Sheet3").Range("P1:Q100")

If Intersect(Target, R) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False

On Error Resume Next

For Each RR In Intersect(Target, R) 'Only check changed cells
RR = Application.VLookup(RR.Value, Vals, 2, False)
Next RR
endit:
Application.EnableEvents = True

End Sub


Gord

 

[Tami]

thanks much. unfortunately, i have about 17 fields (columns) that have Data
Validation and the file has about 500 rows. I was hoping there was an easier
way to provide a description to the user instead of just seeing a list of
valid cryptic codes. i've heard of combo boxes or control boxes??

 

[Gord Dibben]

Correction

'column Q has list of return numbers...........should read

' column Q has list of apparel


Gord

 

[Tami]

sorry i once again made my request confusing...
i want the user to enter or select 02 from a drop doown list and the cell
value would be 02,... i just feel like it would be nice for the user to know
what 02 means by showing a drop down list like this: (a code with a brief
description)

01-Swimwear
02-Pants
03-Tops
04-Jackets
etc

does that make sense?

 

[Gord Dibben]

Do you really have 17x500 DV dropdowns?

If you want to send me a copy of your workbook by email I can have a look.

Should be able to alter the code to cover 17 columns and 500 rows.

gorddibbATshawDOTca

Make the changes to send to my email.


Gord

 

[Gord Dibben]

You could alwys use a message box that pops up when user selects an item.

That too would require code.

Or without code, a VLOOKUP formula in an adjacent cell that returns the
description.


Gord

 

[Xt]

sorry i once again made my request confusing...
i want the user to enter or select 02 from a drop doown list and the cell
value would be 02,... i just feel like it would be nice for the user to know
what 02 means by showing a drop down list like this:   (a code with a brief
description)

01-Swimwear
02-Pants
03-Tops
04-Jackets
etc

does that make sense?

Try this. Make up your drop down validation list using 01, 02, 03
etc. Now format each of these with a custom format "Swimwear" or "01-
Swimwear" if you prefer. The original codes are still stored
internally but they just look different. Now the dropdown list gives
the words and but the codes alone are entered in the cells.

xt

 

[Gord Dibben]

How do you Custom Format text, which 01, 02, 03 is?


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

You could make your entries with a leading apostrophe (eg, '01) in the cells
and then a Custom Format like this...

@" - Swimwear"

The only problem is that the DV List will only show the 01 and not the
formatted text part.

 

[Gord Dibben]

So back to square one which was to have the 01, 02, 03 show a description
when selected.

I think either a helper cell with VLOOKUP or my event code.


Gord

 

[Xt]

How do you Custom Format text, which 01, 02, 03 is?

Gord Dibben  MS Excel MVP

You could leave them as numbers and format them Custom 00

xt

 

[Debra Dalgleish]

There's a sample file here that shows a product ID and product name in
the data validation dropdown.
http://www.contextures.com/excelfiles.html#DV0005

In the sample file, after you select an item, only the product name is
left in the cell. You could change the columns in the sample file, so
the code is left in the cell.

In the lookup table, type an apostrophe at the start of each product
code number, so it will keep the leading zero when you select it in the
drop down list.