IF formula with drop down list

[IsmailA]

how can can i use IF function with the "data validation" drop down list in case of the condition was not met.
in the attached file, the condition is "B is blank", if met so "D8 must be blank", if not met "D8 will be chosen from a drop down list "M8:M38"

Trainees Scoring Sheet - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Training Program
2		Type of Training
3		Date
4		Location
5
6
7	#	Trainee Name	Attendance	Pre-Post Test	Activities	On-Site	Total Score	Note				Attendance	Pre-Post Test	Activities	On-Site
8	1	Manar	All	26	23	35	84					All	0	0	0
9	2	Maged	None	20	15	40	None					None	1	1	1
10	3	Murad	All	18	15	37	70						2	2	2
11	4	Zain	All	20	26	23	69						3	3	3
12	5	Zumurrda	None	14	10	16	None						4	4	4
13	6	Tamer	All				0						5	5	5
14	7	Kamal	None				None						6	6	6
15	8												7	7	7
16	9												8	8	8
17	10												9	9	9
18	11												10	10	10
19	12												11	11	11
20	13												12	12	12
21	14												13	13	13
22	15												14	14	14
23	16												15	15	15
24	17												16	16	16
25	18												17	17	17
26	19												18	18	18
27	20												19	19	19
28	21												20	20	20
29	22												21	21	21
30	23												22	22	22
31	24												23	23	23
32	25												24	24	24
33	26												25	25	25
34	27												26	26	26
35	28												27	27	27
36	29												28	28	28
37	30												29	29	29
38	31												30	30	30
39	32														31
40	33														32
41	34														33
42	35														34
43	36														35
44	37														36
45	38														37
46	39														38
47	40														39
48	41														40
49	42
50	43
51	44
52	45
53	46
54	47
55	48
56	49
57	50
Initial Training

Cell Formulas
Range	Formula
G8:G57	G8	=IF(C8="","",(IF(C8="All",(SUM(D8:F8)),"None")))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G8:G57	Cell Value	between 70 and 100	text	NO
G8:G57	Cell Value	between 0 and 69	text	NO
G8:G57	Cell Value	contains "None"	text	NO
G8:G57	Cell	contains a blank value	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C8:C57	List	=$L$8:$L$9
D8:E57	Whole number	between 0 and 30
F8:F57	Whole number	between 0 and 40

 

[kevin9999]

The following addresses the issue of the Validation List selecting either a blank, or from a list depending on whether column B is blank or not. However, it will still allow a number to be added if column B is blank - I think you will need a VBA solution for that. Depends how complicated you want your sheet to be?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Training Program
2		Type of Training
3		Date
4		Location
5
6
7	#	Trainee Name	Attendance	Pre-Post Test	Activities	On-Site	Total Score	Note				Attendance	Pre-Post Test	Activities	On-Site
8	1	Manar	All	26	23	35	84					All	0	0	0
9	2	Maged	None	20	15	40	None					None	1	1	1
10	3	Murad	All	18	15	37	70						2	2	2
11	4	Zain	All	20	26	23	69						3	3	3
12	5	Zumurrda	None	14	10	16	None						4	4	4
13	6	Tamer	All				0						5	5	5
14	7	Kamal	None				None						6	6	6
15	8												7	7	7
16	9												8	8	8
17	10												9	9	9
18	11												10	10	10
19	12												11	11	11
20	13												12	12	12
21	14												13	13	13
22	15												14	14	14
23	16												15	15	15
24	17												16	16	16
25	18												17	17	17
26	19												18	18	18
27	20												19	19	19
28	21												20	20	20
29	22												21	21	21
30	23												22	22	22
31	24												23	23	23
32	25												24	24	24
33	26												25	25	25
34	27												26	26	26
35	28												27	27	27
36	29												28	28	28
37	30												29	29	29
38	31												30	30	30
39	32														31
40	33														32
41	34														33
42	35														34
43	36														35
44	37														36
45	38														37
46	39														38
47	40														39
48	41														40
49	42
50	43
51	44
52	45
53	46
54	47
55	48
56	49
57	50
Sheet1

Cell Formulas
Range	Formula
G8:G57	G8	=IF(C8="","",(IF(C8="All",(SUM(D8:F8)),"None")))

Cells with Data Validation
Cell	Allow	Criteria
D8:D57	List	=IF($B8="",$P$8,$M$8:$M$38)
E8:E57	List	=IF($B8="",$P$8,$N$8:$N$38)
F8:F57	List	=IF($B8="",$P$8,$O$8:$O$48)

 

[kevin9999]

If you wanted to be sure that a number can't be added in columns D:F if column B is blank, put this worksheet change code in the sheet code module of the Initial Training sheet. Right click the sheet tab name, select View Code & copy the code below to the window that appears on the right of screen. You'll need to save the file as either macro-enabled or binary format for it to work.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D8:F57"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If Cells(Target.Row, 2) = "" Then
            MsgBox "Cannot enter number if Trainee Name is blank"
            Target = ""
            Target.Select
        End If
    End If
    
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[IsmailA]

If you wanted to be sure that a number can't be added in columns D:F if column B is blank, put this worksheet change code in the sheet code module of the Initial Training sheet. Right click the sheet tab name, select View Code & copy the code below to the window that appears on the right of screen. You'll need to save the file as either macro-enabled or binary format for it to work.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D8:F57"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        If Cells(Target.Row, 2) = "" Then
            MsgBox "Cannot enter number if Trainee Name is blank"
            Target = ""
            Target.Select
        End If
    End If
   
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Sorry, as a beginner I have a question. If I enable the macro, how can I be ensured that it will not work on the other excel files.

 

[kevin9999]

Sorry, as a beginner I have a question. If I enable the macro, how can I be ensured that it will not work on the other excel files.

Not only will it not work on other Excel files, it won't even work on any other sheet within the same file.

 

[kevin9999]

Did the proposed solution work for you?

 

[IsmailA]

Did the proposed solution work for you?

Yes, thank you very much.

 

[kevin9999]

Good to hear, and thanks for the feedback