Amending data and locking cells based on drop down selection and checkbox

[nene24713]

Good morning,

I've struggled to solve this with data validation and formulas and believe I need to use VBA to solve this problem? This is something I do not have any experience with and so am really hoping someone will be able to help me?

1. When the checkbox in cell E4 is unchecked I would like cells F25:J25, F26, F28:J29, F31:J31 to be locked and the contents cleared. When the checkbox is checked, the cells should be unlocked.
   
   
2. When 'none' is selected in E5:
   • F37 should be 0.00/cleared and locked
   • F41, F50, F51 should also be cleared and locked
3. When 'self-consumption' is selected in E5:
   • F37 should be unlocked
   • F41, F50, F51 should remain clear/locked
4. When 'overnight-charging' is selected in E5:
   1. F37 should be 0.00/cleared and locked
   2. F41, F50, F51 should be unlocked

Thanks for looking

locking and clearing cells.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4		PRODUCTS	Solar
5			Battery		None
6
7
8
9		AREA	Postcode	Irradiance Zone
10
11			RG	1	Zone 1 - London
12
13
14		BILL	ELECTRICITY NOTE: If solar only installation, use standard rate only.
15
16			Annual Consumption (kWh)			4000.00
17			Standard Rate (pence per kWh)			0.17000
18			Off-Peak Rate (pence per kWh - if applicable)			0.10000
19			Number of Off-Peak Hours			4
20			Average Unit Rate			0.15833
21			Export Tariff			0.055
22
23
24		SOLAR	Number of Arrays			Array 1	Array 2	Array 3	Array 4	Array 5
25			Number of Modules			5	10
26			Module Power (Wp)			370	370	370	370	370
27			Total Installed Cap (kWp)			1.85	3.70
28			Orientation (° from south)			50	70
29			Inclination (° from horizontal)			30	35
30			Irradiance (kk)			914	854
31			Shading Factor (e.g. 0.94)			0.96	1
32			Annual Output			1623.26	3159.80
33			Total Annual Output			4783.06
34
35
36		USE	Solar Self-Consumption			31.36%
37			Battery Self-Consumption			20.00%
38			Export			48.64%
39
40
41		OVERNIGHT CHARGING	Battery Make and Model			Growatt 6.5
42			Battery Size			0.92
43			Depth of Depletion			92.00%
44			Round Trip Efficiency			92.50%
45			Cannibalism (kWh per year)			12
46			Available Charge Daily			0.8
47			Annual Benefit (kWh)			251.84
48			Pre Install Consumption			4000.00
49			Current Customer Rate			0.15833
50			New Standard Rate (pence per kWh)			0.15000
51			New Off-Peak Rate (pence per kWh)			0.05000
52			Annual Benefit (£)			£25.22
53
54
55
Input

Cell Formulas
Range	Formula
D11	D11	=IF($C$11="","",LOOKUP(C11,Formulas!G2:H172))
E11	E11	=IF($D$11="","",LOOKUP(D11,Formulas!J2:K26))
F20	F20	=IF(OR($F$18="",$F$19=""),"",(((F17*(24-F19))+(F18*F19))/24))
G26:J26	G26	=IF($F$26="","",F26)
F27	F27	=IF(OR($F$25="",$F$26=""),"",(F25*F26)/1000)
G27:J27	G27	=IF(OR(G25="",G26=""),"",(G25*G26)/1000)
F30	F30	=IF(OR(F28="",F29=""),"",INDEX(INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$93"),MATCH($F$29,INDIRECT("'"&$E$11&"'!"&"$B$2:$B$93"),0),MATCH($F$28,INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$2"),0)))
G30	G30	=IF(OR(G28="",G29=""),"",INDEX(INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$93"),MATCH($G$29,INDIRECT("'"&$E$11&"'!"&"$B$2:$B$93"),0),MATCH($G$28,INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$2"),0)))
H30	H30	=IF(OR(H28="",H29=""),"",INDEX(INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$93"),MATCH($H$29,INDIRECT("'"&$E$11&"'!"&"$B$2:$B$93"),0),MATCH($H$28,INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$2"),0)))
I30	I30	=IF(OR(I28="",I29=""),"",INDEX(INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$93"),MATCH($I$29,INDIRECT("'"&$E$11&"'!"&"$B$2:$B$93"),0),MATCH($I$28,INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$2"),0)))
J30	J30	=IF(OR(J28="",J29=""),"",INDEX(INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$93"),MATCH($J$29,INDIRECT("'"&$E$11&"'!"&"$B$2:$B$93"),0),MATCH($J$28,INDIRECT("'"&$E$11&"'!"&"$B$2:$AL$2"),0)))
F32:J32	G32	=IF(OR(G28="",G29="",G31=""),"",G27*G30*G31)
F33	F33	=IF(F32="","",SUM(F32:J32))
F36	F36	=IF(F33="","",(Input!F16*0.375)/Input!F33)
F38	F38	=IF(F36="","",100%-F36-F37)
F42	F42	=IF($F$41="","",VLOOKUP($F$41,Formulas!$O$3:EP$9,ROWS($1:3),FALSE))
F43	F43	=IF(F41="","",LOOKUP(F41,Formulas!O3:O3,Formulas!Q3:Q3))
F44	F44	=IF(F41="","",LOOKUP(F41,Formulas!O3:O3,Formulas!R3:R3))
F45	F45	=IF(F41="","",LOOKUP(F41,Formulas!O3:O3,Formulas!S3:S3))
F46	F46	=IF(F41="","",F42*F43*F44)
F47	F47	=IF(F41="","",F46*(365-28)-F45)
F48	F48	=IF(F16="","",F16)
F49	F49	=IF($F$20="",$F$17,$F$20)
F52	F52	=IF(OR(F48="",F49="",F50="",F51=""),"",F47*(F50-F51)-((F50-F49)*F48)/1000)

Cells with Data Validation
Cell	Allow	Criteria
F17:F18	Whole number	between 0.01 and 0.99
F21	Whole number	between 0.01 and 0.99
F50:H51	Whole number	between 0.01 and 0.99
F37	Custom	=IFS(E5="None",0,E5="Overnight Charging",0,E5="Self-Consumption","0")
C11	List	=Formulas!$G$2:$G$172
F41:H41	List	=Formulas!$O$3:$O$9
F28:J28	List	=Formulas!$M$2:$M$37
E5:F5	List	=Formulas!$A$7:$A$9