Validating data with a list (formatting list question)

[cyrilz]

Happy new year to all, especially Chandoo with his "upgraded" familly


I want to allow the user to select a value in a list for a range of cells, but I want the list to be displayed the following way :


CODE1 - Explanation 1

CODE2 - Explanation 2

...


Codex is the value in the cell.


Is there a way (formula / vb?) to do so ?


Cyril Z.

 

[Hui]

Cyrilz


I would add a helper column and concatenate the Code and Explanation together

Assuming Code is in Column A

and Explanation is in Column B

=+CONCATENATE(A2," - ",B2)

 

[cyrilz]

Thanks you Hui,


This will lead to have the full value (code + explanation) in the cell.

I want only the code in the cell without adding an helper column.


I was wondering if this exist in Excel (even VBA).


Cyril

 

[Clarity]

Cyril,


Would you be able to give a slightly more detailed explanation of what you are trying to acheive?

 

[oldchippy]

Hi,


May be using VLOOKUP as explained here


http://chandoo.org/wp/2009/07/20/invoice-numbers/

 

[Hui]

Cyrilz

have a look at


DV0005 - Data Validation "Columns" at the bottom of

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


That has an example that does what you want

 

[cyrilz]

>Hui : That's exactly what I wanted to do. Thanks a lot

 

[cyrilz]

>oldChippy : VLOOKUP doesn't work with Excel's validation lists.

 

[cyrilz]

For all interested, there is a side effect of "cutting" in the cell values returned from a validation list.


You cannot define the width of the drop-down list, which is really annoying with long names.


Is there any way to define this width and also the number of item displayed ?


Cyril Z.

 

[keymaster]

@Cyril thanks for the wishes. Wish you a good year ahead.


Coming to your

Is there any way to define this width and also the number of item displayed ?

you can try form controls or even active-x controls. You can define width and number of items shown easily. Doesnt require any macros. Also you can link the output to a cell. but output will always be a number indicating the position of the selection in the list. You can mash this with vlookup to show CODEx.


For more fancy solutions you can resort to vba...