Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-macros

Hide row / Activate formulas based on whether checkbox is "TRUE/FALSE"

I'm working on a spreadsheet for my car dealership business in an RP Game and I've got everything working how I want besides separating the "Fully Purchased" car sales and the "Financed" car sales.

I want it set up so when someone checks the box under the "Finance?" column, it calculates all of the formulas in the Finance Information table, otherwise, if the box is unchecked I want that particular row in the "Finance Information" table to be clear/hidden/£0 values.

enter image description here

above is an image with a brief annotation of what it looks like/how i want it to work.

I'm fairly new to sheets but I've learnt a lot in the past couple days I've been working on it. I've been messing around with =IF, =COUNTIF, =SUMIF, =SUMIFS, =QUERY and so on desperately trying to make something work to no avail.

Not sure if what I want to do is too farfetched for formulas and will require a script?

Thanks in advance

[EDIT 1]: The formula in the "Down Payment" Column is just =SUM(40%*F4)

Solution

  • I think you can use the value of the check-box as "True" (checked) or "False" (unchecked) in an IF formula. From this blog post if you have the cell with the checkbox selected, you should see "True" or "False" in the formula bar.

    So, I would recommend trying something like this in each of your "Finance Information" columns. Based on your edit, for the Down Payment in row 3:

    =IF(H4, SUM(40%*F4), "")

    Based on a comment, and further reading it looks like you set up the checkbox through Data > Data Validation and assigned the True value to be "Owned". If this is the case, try:

    =IF(H4="Owned", SUM(40%*F4), "")

    This should test for the checked value.