drop list abbreviated from full description

[scorpius68]

I need to create a pick list in a very small cell that is large enough for 3 characters, which represent departments. but the list i need to show the full department name when they scroll to select.

example.

123 - shipping
abc - sales


when they click the cell the drop list would show shipping and sales. If they selected sales the cell would then populate with abc, if shipping it would show 123.

total newb and sure i'll have more tonight :-)

[JBeaucaire]

This is a actually a bit of a hack... you can use Custom Number Formats to secretly display specific text in a cell regardless of that cell's true numeric value. This only works because your need is exactly right... to see text, but have a number be in the cell in reality.


This trick is accomplished in the cells that make up the drop down list. Let's assume the two cells that make up your drop down are M1 and M2.

1) In M1 enter the numeric value 123
2) Now click on M1 and press CTRL-1 to open the cell format wizard
3) Now apply a Custom Number Format of "123 Shipping"

Notice the string is all in quotes? This means no matter what number you type into M1, it will then display the text 123 Shipping.

4) In M2 put in a different number, then repeat steps 2) and 3) above to apply a number format of "abc sales"

5) Now use M1 and M2 as the source of a Data Validation Drop Down list, it will show the TEXT to you, but when you make a selection it will actually show the numbers.

NOTE: This will not work if you want to see one text string and have another text string appear, only if you want a numeric code to appear.

[scorpius68]

let me try another example. using the below list

E - Enhancement
D - Defect
L - Legal / Compliance
N - New


I should see the selections above or even just the names, but when i select 'New' for example. The cell would populate with an 'N', likewise if i selected Legal/Compliance it would populate with an "L".

[JBeaucaire]

No, the hack I suggested only works for seeing text and showing numbers. To do what you're asking would require VBA programming.