Replace cell value based on checkbox

[Casp]

Hello All,

I have a spreadsheet with some formulas that I use to calculate values based on data from my sheets. Here is a question that I could not wrap my head around after hours of research due to the structure of the calculations.

Attached is the file that I am working with. On the tracking tab, Column M has checkboxes listed. In Column P is a calculated amount from my adjustments tab. You may notice that Column N and Column R are hidden as they store the true/false values for the checkboxes.

Here is what I would like to do:

1. If the checkbox is checked (true) in column M, the amount displayed in Column P should be replaced with the fee amount located in the adjustments sheet ($360)
2. Should this fee change to any other amount, any box checked in column M on the tracking sheet should update automatically.
3. If the box is unchecked (false) in column M, there is no change to the number already in populated in that box.

testdoc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M		O	P	Q		S	T
1	Date	Department											Fee			Paid	Billed
2	1/6/2021	Dept. 1	16	4	6	1	9	2	13	13	6	34			$1,100.00	$1,170.00			$70.00	User1
3	1/6/2021	Dept. 2	7	0	8	0	0	0	0	0	68	31			$315.00	$360.00			$45.00	User2
Tracking

Cell Formulas
Range	Formula
O2:P3	O2	=Adjustments!K2
S2:S3	S2	=IFERROR(SUM(P2-O2), "No Data Entered")

Named Ranges
Name	Refers To	Cells
ABSID	=Tracking!$O$2:$O$3	S2
AMBID	=ITRID[[#All],[Column12]]	S2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:P3	Expression	=R2=TRUE	text	NO
O2:S3	Cell Value	contains "No Data"	text	NO
S2:S3	Cell Value	>10	text	NO
A2:S396	Expression	=OR(AND(#REF!<>"All Customers",$B2<>#REF!))	text	YES

Cells with Data Validation
Cell	Allow	Criteria
B2:B3	List	=CUID

testdoc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	row	Amount1	Operation	Column3	Amount2	Operation		Amount3	Amount32		Amount4	Amount42		Fee
2	23	$60.00	Add		$0.00	Add		$1,040.00	$1,170.00		$1,100.00	$1,170.00		$360
3	24	$0.00	Add		$0.00	Add		$315.00	$360.00		$315.00	$360.00
Adjustments

Cell Formulas
Range	Formula
H2:H3	H2	=IFERROR(VLOOKUP(Tracking!B2,Rates,2,0)*Tracking!C2+VLOOKUP(Tracking!B2,Rates,3,0)*Tracking!D2, "No Data Entered")
I2:I3	I2	=IFERROR(VLOOKUP(Tracking!B2,Rates,2,0)*Tracking!E2+VLOOKUP(Tracking!B2,Rates,3,0)*Tracking!F2+VLOOKUP(Tracking!B2,Rates,4,0)*Tracking!G2+VLOOKUP(Tracking!B2,Rates,5,0)*Tracking!H2,"No Data Entered")
K2:K3	K2	=IFERROR(CHOOSE(MATCH(C2,$T$2:$T$5,0),B2+H2,MAX(B2,H2)-MIN(B2,H2),B2*H2,B2/H2),"No Data Entered")
L2:L3	L2	=IFERROR(CHOOSE(MATCH(F2,$T$2:$T$5,0),E2+I2,MAX(E2,I2)-MIN(E2,I2),E2*I2,E2/I2),"No Data Entered")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B3,D2:D51,E2:E305	Cell Value	>0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C2:C3	List	=$T$2:$T$5
F2:F3	List	=$T$2:$T$5

testdoc.xlsx
	A	B	C	D	E
1	name	Price list
2	Department	1	2	3	4
3	Dept. 1	$60.00	$20.00	$70.00	$80.00
4	Dept. 2	$45.00	$30.00	$20.00	$15.00
Department

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A30:A1048576,A25,A22:E24,A26:E29,C25:E25,C3:E3,A1:A21	Expression	=AND(COUNTIF($A:$A, A1)>1,NOT(ISBLANK(A1)))	text	YES

Is this doable?
Thank you all for your help!

 

[gordsky]

Is column "M" the location of the checkbox or is this where the checkbox returns its true or false statement
If the latter which cell contains the result of the checkbox?

 

[Casp]

Is column "M" the location of the checkbox or is this where the checkbox returns its true or false statement
If the latter which cell contains the result of the checkbox?

Hello,
Yes:
- Column M will contain the checkboxes (form control not active x)
- Column N (currently hidden) will contain the True or False result when checked or unchecked
- Column M checkboxes will be linked to its adjacent cell in Column N

 

[gordsky]

change your formula in P2 to be if(N2=true,Value1,Value2)

change Value1 to be your value if N2 is true and Value2 to be your value if N2 is false

 

[Casp]

change your formula in P2 to be if(N2=true,Value1,Value2)

change Value1 to be your value if N2 is true and Value2 to be your value if N2 is false

The issue is, if the checkbox is unchecked, I need that value to remain unchanged in the cell. If the checkbox is checked, then that value should be overwritten by the fee amount.

 

[gordsky]

The issue is, if the checkbox is unchecked, I need that value to remain unchanged in the cell. If the checkbox is checked, then that value should be overwritten by the fee amount.

I understand that so use the formula I gave you. For the false statement use the formula that is in p2 now, for the true value reference the cell holding your fee amount

 

[Casp]

Thanks Gordsky, I didn't know that the formula could be used that way. Very simple and works as expected. Ended up with something like this to reference cell data in my other sheet:

=IF(N2=TRUE,Adjustments!N2, Adjustments!L2)