Excel: Data validation of specific numbers with special cell formatting -

i'm learning data validation of cell values in excel , have discovered problem solve. have cell want allow specific number values. no problem since can use data validation specific cell. can't use "numbers" criteria have numbers 10; 20; 30 example because can specify number ranges (larger than, less than, between etc). instead of using number range use "list" function , write 10; 20; 30 , works.

the problem occurs when add special formatting cell. let's want format 1 isn't pre-existing in excel, enter value "10" should display "10 moneys" or grammatically correct. if use custom formatting can add " moneys" after "standard" displayed in cell format menu (ctrl + 1).

if either 1 works, if add both list 10; 20; 30, , formatting of standard" moneys" doesn't work when use drop down menu. reason because drop down menu tries add value "10 moneys" , list doesn't recognise because expects "10" entered. if change list 10 moneys; 20 moneys; 30 moneys, drop down menu works, can't enter number "10", "20" or "30" manually because list expects entry "10 moneys" etc.

does here have suggestion on how solve this?

i know can use first example , tell people use document manually input numbers want eliminate potential errors far possible.

thank in advance suggestions might have!

the list of values must in named range custom format work. can follow these steps.

1. in column enter values 10, 20, , 30 (in cells a1, a2, a3 example).
2. highlight these cells , press ctrl+f3 bring name manager
3. click "new" , enter name new range (e.g. "myvalues")
4. select range of cells want data validation be
5. select data > data validation, select "list" 'allow' dropdown, , enter name range (e.g. =myvalues) source
6. with same range still highlighted, press ctrl+1 add custom number format (i.e. select "custom" list of categories, , enter general "moneys" in type textbox)

now, whether enter values 10,20, or 30 manually cell, or select them dropdown, should same result ("10 moneys", "20 moneys", or "30 moneys")