Replace cell value based on checkbox

Casp

New Member
Joined
Jul 24, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMOPQST
1DateDepartmentFeePaidBilled
21/6/2021Dept. 116461921313634$1,100.00$1,170.00$70.00User1
31/6/2021Dept. 2708000006831$315.00$360.00$45.00User2
Tracking
Cell Formulas
RangeFormula
O2:P3O2=Adjustments!K2
S2:S3S2=IFERROR(SUM(P2-O2), "No Data Entered")
Named Ranges
NameRefers ToCells
ABSID=Tracking!$O$2:$O$3S2
AMBID=ITRID[[#All],[Column12]]S2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
P2:P3Expression=R2=TRUEtextNO
O2:S3Cell Valuecontains "No Data"textNO
S2:S3Cell Value>10textNO
A2:S396Expression=OR(AND(#REF!<>"All Customers",$B2<>#REF!))textYES
Cells with Data Validation
CellAllowCriteria
B2:B3List=CUID


testdoc.xlsx
ABCDEFGHIJKLMN
1rowAmount1OperationColumn3Amount2OperationAmount3Amount32Amount4Amount42Fee
223$60.00Add$0.00Add$1,040.00$1,170.00$1,100.00$1,170.00$360
324$0.00Add$0.00Add$315.00$360.00$315.00$360.00
Adjustments
Cell Formulas
RangeFormula
H2:H3H2=IFERROR(VLOOKUP(Tracking!B2,Rates,2,0)*Tracking!C2+VLOOKUP(Tracking!B2,Rates,3,0)*Tracking!D2, "No Data Entered")
I2:I3I2=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:K3K2=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:L3L2=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
CellConditionCell FormatStop If True
B2:B3,D2:D51,E2:E305Cell Value>0textNO
Cells with Data Validation
CellAllowCriteria
C2:C3List=$T$2:$T$5
F2:F3List=$T$2:$T$5



testdoc.xlsx
ABCDE
1namePrice list
2Department1234
3Dept. 1$60.00$20.00$70.00$80.00
4Dept. 2$45.00$30.00$20.00$15.00
Department
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A30:A1048576,A25,A22:E24,A26:E29,C25:E25,C3:E3,A1:A21Expression=AND(COUNTIF($A:$A, A1)>1,NOT(ISBLANK(A1)))textYES


Is this doable?
Thank you all for your help!
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

gordsky

Well-known Member
Joined
Jun 2, 2016
Messages
530
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 24, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jun 2, 2016
Messages
530
Office Version
  1. 2016
Platform
  1. Windows
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
 
Solution

Casp

New Member
Joined
Jul 24, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jun 2, 2016
Messages
530
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jul 24, 2021
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
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:
Excel Formula:
=IF(N2=TRUE,Adjustments!N2, Adjustments!L2)
 

Forum statistics

Threads
1,175,663
Messages
5,898,756
Members
434,729
Latest member
Tej92

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