IF; not for a cell, for a table

[mohtogh]

Hi Guys,
If I want to check a cell is negative or positive I write:
=IF(A1>0,"OK","ERROR")
But I want to check a table,if there is even one negative number, I see ERROR.
Is that possible?

Thanks

 

[enirwin]

You could try conditional formatting. For example, you can set the format for each cell in the table so the text turns red if the cell is less than zero. This would help to easily identify negative numbers in a table. Its not exactly what you were looking for, but it might help.

Conditional formatting is under the format menu.

 

[GregLocock]

Sure

why not try something like

=if((product(a1:b2))<0,"Error","OK")

works for me


Cheers

Greg Locock

 

[GregLocock]

Oops no it doesn't!

you need to use countif

=if(COUNTIF(a3:B6,"<0")>0,"error","ok")



Cheers

Greg Locock

 

[johnwm]

To catch the error as it occurs:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then
Select Case Target.Row
Case 2 To 6
If Target.Value < 0 Then
MsgBox "Must be non-negative"
Target.Select
End If
End Select
End If
End Sub

This is set for Column 4 (D) and rows 2 to 6

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[mohtogh]

Thanks all.

 

[jghrist]

If you don't need to know where the negative number is, why not simply:

IF(MIN(table_range)>0,"OK","ERROR")

 

[ivymike]

drat, I was hoping nobody had posted that yet!