Format dependent on another cell

[randy700]

Not sure how to build a formula in data validation.
I'm not even convinced this can be done in Excel.

Basically, what I need is:
If A1 = "SOLD" Then A2 must be numeric, must be greater than 0, and cannot be blank.

I've looked at examples of data validation, but can't find anything similar.
Anyone know of a method that will work?



Randy

 

[SkipVought]

Otherwise what?

 

[randy700]

Otherwise, anything goes.

Yes, I know that doesn't make much sense but that's what he wants!
I could live with:

ELSE A2 = "" or
ELSE A2 = 0



Randy

 

[SkipVought]

So help me understand the sequence of things.

What's in A2 when the user enters SOLD?

That's the first question.

When the user enters SOLD, what's supposed to happen?

That's the second question.

Please answer both questions and add whatever else may be relevant.

 

[mintjulep]

And what is supposed to happen WHEN someone enters A2 before they enter A1?

 

[combo]

You need a formula that returns TRUE/FALSE basing on contents of A1 and A2. Some examples are here:

http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010072600.aspx#BM4_7

and here:

http://www.contextures.com/xlDataVal07.html.

Mind that there are some limitations in data validation, not permitted data can be still pasted, and, in your case, changing data in A1 does not cause alert if data in A2 stops to satisfy validation conditions.

combo

 

[Andrzejek]

Defining and setting all and complete rules is 99% of the battle. Converting it to formulas or code is then a piece of cake. I am still amazed users don’t realize it.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[randy700]

I'm not getting clear answers when I pose the questions you suggest I ask.
He says he has solved the problem, using sharepoint (which I've never used).

Here is the solution he claims:
=IF([Asset Status]="Sold",[Salem Amount]>0,TRUE)

I don't know where or how he implements this formula.
Thanks for all the suggestions.

I was just posting this question to help a co-worker.
Do not have all the details necessary to provide for you to help find an Excel solution.



Randy

 

[SkipVought]

If he's happy ...

But these issues remain.

 

[Salut39]

Hi,

Try this in data validation (custom) =AND(A1="SOLD",A2<>"",ISNUMBER(A2)=TRUE,A2>0)

Yuri