How to make a Value Specific cell in excel?? It's URGENT..?

Question

I want to code a cell so that it could take only one of the values(0,2,4,6,8,10,NA) as input. If any other value is entered then it should either show error OR change the color of the same cell(in which the value is being entered) 'RED' OR empty the cell.

Please help fast, it's IMPORTANT.

Answer 1

Just select the cells where you want this conditon. Go to Data,

and then in Data tab, go to data validation tab, in Data Tools, next to remove duplicate tab. Please select this, and in the emerging box, in the allow box, select custom, and in the formula box, just enter

=AND(OR(A1=0,A1=2,A1=4,A1=6,

A1=8,A1=10,A1="Na"),A1<>"")

I have started my cell from a1 in my example. You could select according to your choice and change the formula of your first cell selected in a range

and then click ok. you could do it

the formula is given in two lines. there should not be any break in the formula. This was given in two lines to avoid formula truncating. ok enjoy

Answer 2

Select Data menu then Validation . Select Allow List then type in the list of values in the box provided. Apply to required cells Any data that does not match your list will cause an error message to appear or you can customise the events to happen if entered data does not match.

Answer 3

I would create a drop-down list and force specific data to be entered.



http://office.microsoft.com/en-gb/excel-help/create-or-remove-a-drop-down-list-HP005202215.aspx