if statement()

[OWAIZ]

Hi Guys,
i have a small query, i am inserting a formula in a excel report i.e. if (b10, b11, b12, b15, c11, c22, d33 = blank, "Report Incomplete" Report Complete.

How can i go about logically and how to use the IF formula..

Pls guide.

Thanks & Regards
owaiz

 

[DonkeyOte]

you can embed up to 7 IF's within one formula.

The key to remember is the style...

IF(Test, Do If Test True, Do If Not True)

If embedding - instead of putting in the bit relating to Do If Not True - put in the next IF.

So say a=2

IF(A=1,"a equals 1",IF(A=2,"a equals 2","a equals neither 1 nor 2))

 

[DonkeyOte]

Sorry misread this...

You want IF(OR(A1="",A2="",A3=""),"Incomplete","Complete")

Or if it's conditional on ALL cells being blank then

IF(AND(A1="",A2="",A3=""),"Incomplete","Complete")

 

[Jim North]

This is another option for you:

Take you 7 cells and create a named range for them call RqdData. Then use this formula to ensure all 7 have data in them:

=IF(COUNTA(RqdData)=7,"ok","error")

 

[OWAIZ]

Hi THere,
i tried working on the formula suggested by u, it works, but even one of the cell is filled in it's says:report complete: i want until and unless all the fields are filled in it should not show report complete..

pls help

thanks & Regards
owaiz

 

[lenze]

How about a VBA approach:

This code will require that a value be entered in a cell. This is useful when you create a form for others to fill out and it has required fields. Place this code in the sheet module. Substitute the correct cell addresses.

Option Explicit
Dim Mandatory As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
Select Case Target.Address
Case "$A$5", "$A$10", "$A$15", "$A$20", "$A$25", "$A$30"
Set Mandatory = Target
Case Else
If Not Mandatory Is Nothing Then
If Mandatory = "" Then
Mandatory.Select
MsgBox "You cannot leave this cell blank"
End If
End If
End Select

End Sub