Dependent Dropdown styled, filtered sum advanced annual budget tracker

Question

I've been trying to look for Dependent drop-down scripts to use, but I cannot find one that works. I ran out of ideas with this.

Things I need help with

I am trying to make a part in my budget sheet where when you select a category from a data validation drop-down list, the next cell will have a subcategory drop-down list based on the category that was just chosen. In the sheets "EJan," "EFeb", etc.
I want to add everything relevant to the Subcategory in "EJan", and paste the Sum of it in the desired Subcategory and month in "expenses."

Here is a link to a copy of my google spreadsheet

marikamitsos marikamitsos · Accepted Answer · 2020-02-03T08:34:59

I quite often use this script by Jason Jurotich.
You can find it in the description on his YouTube channel:
DYNAMIC DEPENDENT DROP DOWN LISTS IN GOOGLE SPREADSHEETS

Please have a look at a copy of your sheet I prepared and is using the above mentioned script.

https://docs.google.com/spreadsheets/d/1JvJunjYBYdgRY66Nx75UBjHjGDiYL0KascwV3YGuGRs/edit?usp=sharing

This is an exact copy of the above mentioned script:

function depDrop_(range, sourceRange) {
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
    range.setDataValidation(rule);
}

function onEdit() {
    var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
    var aColumn = aCell.getColumn();
    if (aColumn == 1 && SpreadsheetApp.getActiveSheet()) {
        var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
        var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
        depDrop_(range, sourceRange);
    } else if (aColumn == 2 && SpreadsheetApp.getActiveSheet()) {
        var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
        var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
        depDrop_(range, sourceRange);
    }


    //if (aColumn == 8 && SpreadsheetApp.getActiveSheet()){
    //var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
    //var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    //depDrop_(range, sourceRange);
    //}
    //else if (aColumn == 9 && SpreadsheetApp.getActiveSheet()){
    //var range = SpreadsheetApp.getActiveSheet().getRange(aCell.getRow(), aColumn + 1);
    //var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
    //depDrop_(range, sourceRange);
    //}

}

Dependent Dropdown styled, filtered sum advanced annual budget tracker

1 Answers