CODEWITHSUNDEEP
google-sheetsfilterarray-formulas
o ril
o ril

Reputation: 25

Arrayformula Filter but with dynamic values Google Sheets

Here is what I'm trying to do in this sheet: https://docs.google.com/spreadsheets/d/1uK7poWPaGvIR_v-7eiD84zdMXu6AFNJqSZWJff6FxiY/edit?usp=sharing

In "Task Tracker" tab, team members write tasks, hours they did in that task and a count of that task. In "Sheet1" tab, the tasks are automatically populated in the "Tasks This Week" section, and then those are chooseable as drop downs in the "Tasks" section. (This drop down is needed for something I'm building later to keep values in the cells consistent).

The trouble now is that I want to have the "Hours" and "Count" of Monday, Tuesday, Wednesday etc populate automatically based off of the values entered in the "Task Tracker" sheet. But, given that week-by-week, the values (name of tasks) may change, new tasks will arise, I don't have a static value I can filter by.

Is there a way I can use arrayformula+filter to do this, or another function? I want Monday, Tuesday, etc's "Hours" and "Count" in "Sheet 1" to populate automatically, tallying all of the values in the "Task Tracker" tab based off what task is selected in the drop down in the "Tasks" section of Sheet1.

Upvotes: 0

Views: 300

Answers (1)

RalphD
RalphD

Reputation: 46

We can simply take the value of the "Tasks" column and filter the values in our Task Tracker sheet with that specific task.

The rest is syntactic sugar to make sure we deal with empty cells, tasks that don't occur within the Task Tracker even once, and making sure the correct rows and columns are dynamic/static for easy adjustments as we fill out each day.

Here's the code:

=IF(
    ISBLANK($A6),
    "",
    IFERROR(
        SUM(
            FILTER('Task Tracker'!D$3:'Task Tracker'!D$32,
                   'Task Tracker'!$B$3:'Task Tracker'!$B$32 = $A6)
         ),
         0
    )
)

And here's the sheet: https://docs.google.com/spreadsheets/d/1yqg07r0q-2i24Wpv8AQhJjP39uynVBQCJGKEROsp6vM/edit?usp=sharing

Upvotes: 1

Related Questions