if statement()

OWAIZ

Board Regular
Joined
Jun 20, 2002
Messages
89
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Board Regular
Joined
Jun 20, 2002
Messages
89
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

Legend
Joined
Feb 18, 2002
Messages
13,690
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
 

Forum statistics

Threads
1,143,640
Messages
5,719,985
Members
422,256
Latest member
downeybm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top