If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Validation drop-down boxes
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 |
#12
|
|||
|
|||
Validation drop-down boxes
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 |
#13
|
|||
|
|||
Validation drop-down boxes
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. |
#14
|
|||
|
|||
Validation drop-down boxes
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 |
#15
|
|||
|
|||
Validation drop-down boxes
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 |
|
Thread Tools | |
Display Modes | |
|
|