Validation Formula

**swordswinger710** · 04-09-2008, 04:36 PM

I need Cell A2 to only allow a number with a dash if Cell A1 has any number in it. What formula would I use for this?

**Portuga** · 04-09-2008, 06:03 PM

Maybe this will work:

1- First format A2 as "0-" (delete the quote signs)
2- in A2 Validation, insert the following formula in the custom section:
=isnumber(a1)=isnumber(a2)

**swordswinger710** · 04-21-2008, 03:44 PM

I can't enter anything into Cell A2 now - maybe I wasn't clear enough. Here's my situation:

Cell A1 will not always have a number entered into it. Cell A2 will. If Cell A1 receives a number, then Cell A2 must not allow any number that doesn't include a "-" in it somewhere.

For Example:

A1=
A2=12345

A1=12345
A2=12345-1 (Had I entered 12345 here I would have received an error message saying I need a dash since Cell A1 is filled in.)

Is this possible?

**DCSwearingen** · 04-21-2008, 04:49 PM

Originally Posted by Juda

I can't enter anything into Cell A2 now - maybe I wasn't clear enough.

See the logic on Sheet1

**swordswinger710** · 04-22-2008, 09:32 AM

Incredible! It took me a bit because my cells were formatted as text instead of general, but once I figured that out, it worked! Thanks a lot.

Now since we're onto the validation subject, I have yet another question. In many of the cells where I have formulas entered, I have validated them to allow only whole numbers less than zero to prevent users from accidentally changing or deleting the formulas. This seems to work fine until someone hits delete, which allows the user to erase the formula and get away with it. I cannot use protection because then I can't merge cells, so I'm wondering if it's possible to use something else in the Validation field which won't allow anyone to change or delete the formula I worked so hard to come up with.

**DCSwearingen** · 04-22-2008, 07:24 PM

Originally Posted by Juda

Now since we're onto the validation subject, I have yet another question. In many of the cells where I have formulas entered, I have validated them to allow only whole numbers less than zero to prevent users from accidentally changing or deleting the formulas. This seems to work fine until someone hits delete, which allows the user to erase the formula and get away with it. I cannot use protection because then I can't merge cells, so I'm wondering if it's possible to use something else in the Validation field which won't allow anyone to change or delete the formula I worked so hard to come up with.

I would suggest you post this as a new question on the General forum.

My reason for making this suggestion is that I have played around and I can't find a solution to this question. Paraphrasing what one of the regulars has in his signature: There are always solutions, the problem is finding the one that works.

Now, good posting etiquette calls for you to explain that this is a cross post, why you are posting in the new forum (new question on an old thread...??), and provide a link to this thread. Finally, when answered satisfactorily make sure both threads show the solution. This can be accomplished with a link to the thread with the solution.

I hope that's not too confusing. If you want more explanation, send me a PM or e-mail here

Hopefully the response at http://www.excelforum.com/showthread...34#post1911234 will resolve this.

**DCSwearingen** · 04-23-2008, 11:18 AM

Originally Posted by Juda

I need Cell A2 to only allow a number with a dash if Cell A1 has any number in it. What formula would I use for this?

I recently came across this. It has a lot of different tips on validation.

I know I saw a User Defined Function that would identify if a cell had a formula, but I can't find it right now. The context was to use it in conditional formatting, but I am sure it could also be used in Data Validation as it was a true false function.