Problems creating a dropdown box (data validation)...

[BadBoy House]

We've got a dropdown box (data validation - list) which is picking up data from a specific column. The data in the column is in the following format:

1 Sales
2 Contracts
18 Opening Stock
27 Purchase
etc

When we select an entry from the dropdown box the cell obviously displays both the number and the text description to the right.

What we need it to do is just display the number after selecting an item from the list. The text is descriptive and to make it easier for users.

Is there a way of configuring the dropdown box (or another way) so that say if a user selects "1 Sales" from the dropdown it just displays "1" in the cell.

thanks in advance.

[martindwilson]

youd need to use another cell next to the validation to extract /give the number
say validation drop down is in a1 one way is to put
=--LEFT(A1,FIND(" ",A1)-1) which will extract the number as a number not text
if its not always in that format you could refer to a vlookup table somwhere instead

[oldchippy]

Hi,

The text is descriptive and to make it easier for users

Why not do a validation list with the descriptions only, then have a VLOOKUP in another cell to get the number from a table?

[BadBoy House]

cheers fellas - the vlookup combo worked a treat.

thanks very much for your help!!!

[oldchippy]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[themaniraj]

I am at a very basic level, would be grateful if you could explain what you mean in the answer above. I have the same question and cant figure out. Pretty new with using the function and codes.

thank you in anticipation

[themaniraj]

Hi,



Why not do a validation list with the descriptions only, then have a VLOOKUP in another cell to get the number from a table?

I am at a very basic level, would be grateful if you could explain what you mean in the answer above. I have the same question and cant figure out. Pretty new with using the function and codes.

thank you in anticipation

[Cutter]

Welcome to the Forum, themaniraj. Unfortunately you've inadvertently broken one of our rules. Please read the following:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.