cell will only allow certain criteria

**ghynes** · 08-23-2006, 10:08 AM

hello.

can someone tell if its possible to set up cells so that when a person enters data in the cell that it will only let them enter the data if it matches certain criteria?
for eg. i need to have an C before every number that is entered in a cell. can i set the cell up to give an error if a C is not entered in the cell?

**oldchippy** · 08-23-2006, 10:18 AM

Originally Posted by ghynes

hello.

can someone tell if its possible to set up cells so that when a person enters data in the cell that it will only let them enter the data if it matches certain criteria?
for eg. i need to have an C before every number that is entered in a cell. can i set the cell up to give an error if a C is not entered in the cell?

Hi ghynes,

Select the cells where you want the data entered, then go to Data > Validation..., in the Allow box, pick "Custom", then in the formula box enter

="C"&"*" , if you want to put any messages to the other tabs you may, if not just click OK.

Now when you pick one of the cell and type anything without C in front of it you will get an error message.

Hope this helps you, let me know if you need more information

oldchippy

**ghynes** · 08-23-2006, 10:35 AM

using the formula ="C"&"*" wont allow me write anything in the cell at all

**oldchippy** · 08-23-2006, 10:46 AM

Originally Posted by ghynes

using the formula ="C"&"*" wont allow me write anything in the cell at all

Hi ghynes,

I'm very sorry, you are right it doesn't work, must be going mad. I'll try and think of another way or maybe someone else knows - hopefully

oldchippy

**oldchippy** · 08-23-2006, 11:16 AM

Originally Posted by ghynes

using the formula ="C"&"*" wont allow me write anything in the cell at all

OK, I may have the answer now in the formula box, type this

=AND(LEFT(A1,1)="C",LEN(A1)>1)

this will allow "C" or "c" to be entered followed by any number or letter, but the first letter must always be C

Are we getting there?

oldchippy