Check box based on value in other cells

Google Sheets
1

How can I create an automated checklist on the “dashboard” of my budget that will check the box when a bill comes through on my Tiller transaction list?
For example, my current budget (based on the budget template in Google Sheets) has a front page/dashboard where I’ve added a list of our monthly expenses that are relatively “fixed,” are on autopay, and don’t need to be categorized.

Screenshot 2023-11-02 at 9.57.39 AM

So I want the checkboxes to check themselves based on values in our Transactions sheet filled by Tiller. So for example, if I download transactions from Tiller and the words “State Farm” are in the description, the check box beside “Car Insurance” would get checked - or if “T Mobile” appears, the cell phone box will check. That way i know at a glance that they have actually been paid, and if I want to know the details about amounts I can always go digging in the transaction list.

I appreciate any help!

2

Update: I remembered that ChatGPT can write code for you :slightly_smiling_face: - it provided this script and then recommended I set a time-based trigger.
Copying it here on the off chance someone else could use it! (obviously you’d have to customize the sheet names, ranges, keywords, etc.)

function updateCheckboxes() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var budgetSheet = ss.getSheetByName("Budget");
  var transactionsSheet = ss.getSheetByName("Transactions");
  var range = transactionsSheet.getRange("C:C").getValues(); // Assuming the data is in column C

  // Define an array of objects to map keywords to target cells
  var keywordsToCheck = [
    { keyword: "State Farm", cell: "P32" },
    { keyword: "Tmobile", cell: "P31" },
    { keyword: "JPMorgan", cell: "P30" },
    // Add more keywords and target cells as needed
  ];

  for (var i = 0; i < keywordsToCheck.length; i++) {
    var keyword = keywordsToCheck[i].keyword;
    var targetCell = budgetSheet.getRange(keywordsToCheck[i].cell);
    var checked = false;

    for (var j = 0; j < range.length; j++) {
      if (range[j][0].toString().toLowerCase().includes(keyword.toLowerCase())) {
        checked = true;
        break; // No need to continue checking if we found a match
      }
    }

    targetCell.setValue(checked);
  }
}
1 Like
3

If all you want is to check a box if a transaction contains the words, e.g., ‘State Farm’ and occurs in the current calendar month, you can use a formula like this in each cell, select all the cells that should have checkboxes, and set Data → Data validation to Checkbox:

=if(
  count(
    query({ Transactions!B$2:B, Transactions!C$2:C },
      "select Col1 
        where Col1 > date '" & text(EOMONTH(today(),-1),"YYYY-MM-DD") & "' 
        and Col2 contains 'State Farm' "))
  >0, TRUE, FALSE)

If needed, change Transactions!B$2:B to refer to the Date column on your Transactions sheet, and Transactions!C$2:C to refer to the Description column.

Change 'State Farm' to whatever text you want to match; it’s case-sensitive but will find partial matches.