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.
Is this doable?
Thank you all for your help!
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!
Last edited: