Data entry and validation with custom number formatting

[Haui]

Hi,

I am seeking a solution for the following topic:

- two cells have data validation set up as a list being {1,2,3,4,5}
- the first call has general number format while the second ons is formatted as a custom number format being: "0;;-"
- data entry in these cells is now possible either via the drop-down menu or directly into the cell
- however: while in the first cell manual data entries are possible as {1,2,3,4,5}, I can enter into the cell with custom number formatting only {1,2,3,4), i.e. neither the entries "0" nor its formatted 'value' of "-" are being permitted by the data validation

Is there a way to set this up differently, format it differently etc. so that I can input values into the cell containing the data validation also manually and not only via the drop-down (ideally in both ways, i.e. in the form of the actual data valiation ("0") as also in the form of the formatted 'value' ("-"))?

Thanks

 

[LTDLogan]

Is there a way to set this up differently, format it differently etc. so that I can input values into the cell containing the data validation also manually and not only via the drop-down (ideally in both ways, i.e. in the form of the actual data valiation ("0") as also in the form of the formatted 'value' ("-"))?

So I do not entirely understand this question but I do have two suggestions.
First: If you want your drop-down options to be formatted a certain way, rather than setting up a custom number format you could just format the selections correctly in the Data Validation list.
Second: You can allow custom entries in a data validated column by following the steps below

Select all of the applicable cells in the column, or the whole column
Go to the ribbon and select Data>Data Tools>Data Validation (looks like a check mark and no sign)
When the window pops up you can modify the list of acceptable entries and you can allow custom text by selecting the "Error Alert" and changing the style "Stop" to "Warning"
I hope that helps!
best,
Logan