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?

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

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" wrote:

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

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





On Sat, 19 Sep 2009 10:27:01 -0700, Tami
wrote:

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" wrote:

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

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" wrote:

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





On Sat, 19 Sep 2009 10:27:01 -0700, Tami
wrote:

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" wrote:

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

Correction

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

' column Q has list of apparel


Gord

On Sat, 19 Sep 2009 10:46:32 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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





On Sat, 19 Sep 2009 10:27:01 -0700, Tami
wrote:

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" wrote:

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

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" wrote:

Correction

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

' column Q has list of apparel


Gord

On Sat, 19 Sep 2009 10:46:32 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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





On Sat, 19 Sep 2009 10:27:01 -0700, Tami
wrote:

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" wrote:

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

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


On Sat, 19 Sep 2009 11:15:01 -0700, Tami
wrote:

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" wrote:

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





On Sat, 19 Sep 2009 10:27:01 -0700, Tami
wrote:

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" wrote:

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

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


On Sat, 19 Sep 2009 11:32:01 -0700, Tami
wrote:

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" wrote:

Correction

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

' column Q has list of apparel


Gord

On Sat, 19 Sep 2009 10:46:32 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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





On Sat, 19 Sep 2009 10:27:01 -0700, Tami
wrote:

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" wrote:

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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:

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?

On Sep 20, 6:32*am, Tami wrote:
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

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


Gord Dibben MS Excel MVP



On Sat, 19 Sep 2009 14:07:18 -0700 (PDT), Xt
wrote:

On Sep 20, 6:32*am, Tami wrote:
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

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.

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
How do you Custom Format text, which 01, 02, 03 is?


Gord Dibben MS Excel MVP



On Sat, 19 Sep 2009 14:07:18 -0700 (PDT), Xt
wrote:

On Sep 20, 6:32 am, Tami wrote:
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

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

On Sun, 20 Sep 2009 13:21:50 -0400, "Rick Rothstein"
wrote:

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.

On Sep 20, 10:22*am, Gord Dibben <gorddibbATshawDOTca wrote:
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

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.



Tami wrote:
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" wrote:


Correction

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

' column Q has list of apparel


Gord

On Sat, 19 Sep 2009 10:46:32 -0700, Gord Dibben <gorddibbATshawDOTca wrote:


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





On Sat, 19 Sep 2009 10:27:01 -0700, Tami
wrote:


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" wrote:


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


On Sat, 19 Sep 2009 09:49:01 -0700, Tami
wrote:


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?





--
Debra Dalgleish
Contextures
www.contextures.com/tiptech.html
Blog: http://blog.contextures.com