Only showing n characters from data validation dropdown in Excel

Question

I have an excel question.

I have a drop down list using data validation from predefined cells, but want to limit it to one character.

For example, my drop down currently lets me select ('Not Started', 'In Progress', or 'Complete'), but I would like it to show only 'N', 'I', or 'C' respectively.

Normally I would use the Left operation, but how does one combine this with data validation?

Answer 1

To create a list, you must have a valid range in your Excel-sheet. I would create a seperate range with the values you need, and use that as input for the list. I assume you have a list of the full values. You can make a new list with just the first characters with this formula:

=LEFT(A1:A3,1)

Then use that range (In my case B1:B3) as your input for the dropdown