Cell should be blank in data validation

[Zubair]

Dear Experts,

Please change the formula, I want the cells B3:B5 should be blank while selecting "Most Profitable" in B2, similarly if B2 is other then "Most Profitable" B6 should be blank.

Excel help with CHATGPT 3.xlsx
	A	B	C	D	E	F	G	H
1	User selection				Number of Scenario	Number of Years	Business Model	Number of columns
2	View	Most Profitable			Scenario 1	Y1	DF	1
3	Number of Scenario	Scenario 3			Scenario 2	Y2	SF	2
4	Number of of Years	Y3			Scenario 3	Y3	CF	3
5	Business Model	DF			Scenario 4	Y4		4
6	Number of columns	10			Scenario 5	Y5		5
7					Scenario 6	Y6		6
8					Scenario 7	Y7		7
9					Scenario 8	Y8		8
10					Scenario 9	Y9		9
11					Scenario 10	Y10		10
12					Scenario 11	Y11		11
13					Scenario 12	Y12		12
14					Scenario 13	Y13		13
15					Scenario 14	Y14		14
16					Scenario 15	Y15		15
17					Scenario 16	Y16		16
18					Scenario 17	Y17		17
19					Scenario 18	Y18		18
20					Scenario 19	Y19		19
21					Scenario 20	Y20		20
22								21
23								22
24								23
25								24
26								25
27								26
28								27
29								28
30								29
31								30
32								31
33								32
34								33
35								34
36								35
37								36
38								37
39								38
40								39
41								40
Multiple condiation data valida

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years, Years by scenario, Most Profitable
B3	List	=IF($B$2="Most Profitable","",E2:E21)
B4	List	=IF($B$2="Most Profitable","",F2:F21)
B5	List	=IF($B$2="Most Profitable","",G2:G4)
B6	List	=IF($B$2="Most Profitable",H2:H41,"")

 

[Sergius]

Greetings! I have reworked your lists a little. In the name of the name, he created four parameters List1, List2, List3, List4 data taken from Sheet1.
Also, four parameters "_list1", "_list2", "_list3", "_list4" were created in the dispensary.
List1

=Sheet1!$E$2:$E$21

List2

=Sheet1!$F$2:$F$21

List3

=Sheet1!$G$2:$G$4

List4

=Sheet1!$H$2:$H$41

The logical formula is recorded for them.
_List1

=IF(Sheet1!$B$2="Most Profitable";"";List1)

_List2

=IF(Sheet1!$B$2="Most Profitable";"";_List2)

_List3

=IF(Sheet1!$B$2="Most Profitable";"";_List3)

_List4

=IF(Sheet1!$B$2="Most Profitable";List4;"")

For B3, B4, B5, B6, lists were created, where "_list1", "_list2", "_list3", "_list4" are presented in the verification of these.
If in B2 "MOST PROFITABLE", B3, B4, B5 you will not be able to choose anything and they will be empty, and in B6 you will be able to use the choice.

Book1
	A	B	C	D	E	F	G	H
1	User selection				Number of Scenario	Number of Years	Business Model	Number of columns
2	View	Most Profitable			Scenario 1	Y1	DF	1
3	Number of Scenario				Scenario 2	Y2	SF	2
4	Number of of Years				Scenario 3	Y3	CF	3
5	Business Model				Scenario 4	Y4		4
6	Number of columns				Scenario 5	Y5		5
7					Scenario 6	Y6		6
8					Scenario 7	Y7		7
9					Scenario 8	Y8		8
10					Scenario 9	Y9		9
11					Scenario 10	Y10		10
12					Scenario 11	Y11		11
13					Scenario 12	Y12		12
14					Scenario 13	Y13		13
15					Scenario 14	Y14		14
16					Scenario 15	Y15		15
17					Scenario 16	Y16		16
18					Scenario 17	Y17		17
19					Scenario 18	Y18		18
20					Scenario 19	Y19		19
21					Scenario 20	Y20		20
22								21
23								22
24								23
25								24
26								25
27								26
28								27
29								28
30								29
31								30
32								31
33								32
34								33
35								34
36								35
37								36
38								37
39								38
40								39
41								40
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years;Years by scenario;Most Profitable
B3	List	=_List1
B4	List	=_List2
B5	List	=_List3
B6	List	=_List4

 

[Zubair]

Hi Thanks, Many thanks I am unaware how to put VBA codes you have provided, can you please guide ?

 

[Sergius]

On the Formulas tab, in the Defined Names group, click Define Name.
In the New Name dialog box, in the Name box, type the name.
In the Refers to box type = and then type the formula

Thus create 8 names. Here is their listing
List1

=Sheet1!$E$2:$E$21

List2

=Sheet1!$F$2:$F$21

List3

=Sheet1!$G$2:$G$4

List4

=Sheet1!$H$2:$H$41

_List1

=IF(Sheet1!$B$2="Most Profitable";"";List1)

_List2

=IF(Sheet1!$B$2="Most Profitable";"";List2)

_List3

=IF(Sheet1!$B$2="Most Profitable";"";List3)

_List4

=IF(Sheet1!$B$2="Most Profitable";List4;"")

As a result, there will be such a picture

For B2, B3, B4, B5, B6, create lists. For B2 with a list of points. For B3, B4, B5, B6, prescribe “_List1”, “_List2”, “_List3”, “_List4”, respectively.
There is one nuance to create lists for B3, B4, B5, the B2 cell should be empty, and when creating a list for B6, on the contrary, "Most Profitable" should be spelled out.

 

[Zubair]

Hi, I have applied your given formula, but its not showing blank in case of "Most Profitable", please check what mistake I am doing.

Excel help with CHATGPT 3.xlsx
	A	B	C	D	E	F	G	H
1	User selection				Number of Scenario	Number of Years	Business Model	Number of columns
2	View	Scenario by years			Scenario 1	Y1	DF	1
3	Number of Scenario	Scenario 4			Scenario 2	Y2	SF	2
4	Number of of Years	Y8			Scenario 3	Y3	CF	3
5	Business Model	CF			Scenario 4	Y4		4
6	Number of columns	7			Scenario 5	Y5		5
7					Scenario 6	Y6		6
8					Scenario 7	Y7		7
9					Scenario 8	Y8		8
10					Scenario 9	Y9		9
11					Scenario 10	Y10		10
12					Scenario 11	Y11		11
13					Scenario 12	Y12		12
14					Scenario 13	Y13		13
15					Scenario 14	Y14		14
16					Scenario 15	Y15		15
17					Scenario 16	Y16		16
18					Scenario 17	Y17		17
19					Scenario 18	Y18		18
20					Scenario 19	Y19		19
21					Scenario 20	Y20		20
22								21
23								22
24								23
25								24
26								25
27								26
28								27
29								28
30								29
31								30
32								31
33								32
34								33
35								34
36								35
37								36
38								37
39								38
40								39
41								40
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years, Years by scenario, Most Profitable
B3	List	=_List1
B4	List	=_List2
B5	List	=_List3
B6	List	=-List4

 

[Sergius]

Simple methods will not work. As an option to use conditional formatting. Check B2 for a specific text and set the color of the font with white for the necessary cells.

Запрет выбора списка.xlsx
	A	B	C	D	E	F	G	H
2	View	Scenario by years			Scenario 1	Y1	DF	1
3	Number of Scenario				Scenario 2	Y2	SF	2
4	Number of of Years				Scenario 3	Y3	CF	3
5	Business Model				Scenario 4	Y4		4
6	Number of columns	4			Scenario 5	Y5		5
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B5	Expression	=$B$2="Most Profitable"	text	NO
B6	Expression	=$B$2<>"Most Profitable"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B2	List	Scenario by years;Years by scenario;Most Profitable
B3	List	=_List1
B4	List	=_List2
B5	List	=_List3
B6	List	=_List4

 

[Zubair]

That I have already done, many thanks for your time and solution I am marking this as solved.

 

[Sergius]

Happy to help.

 

[mumps]

If you would like to try a macro, then remove the conditional formatting and copy and paste the macro below into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in B2 and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Address(0, 0) <> "B2" Then Exit Sub
    If Target = "Most Profitable" Then
        Range("B3:B5").ClearContents
    Else
        Range("B6").ClearContents
    End If
End Sub

 

[Zubair]

Many thanks mumps