Search code examples
excelexcel-2013countif

Selecting a range based on the reference of one cell

I have an excel file. I don't want to write any VBA code, as I don't necessarily want to run a macro for the process to work; I want it to automatically update information as I change one cell (the "Date" cell on the second sheet/photo).

So, basically I have a tracker that I will use to determine for any given date, how many tasks do I have issued to each company (military companies for context). It'll help me understand who has the least amount of tasks in general and what percentage of every company is dished out to tasks. I have a tracker of those issued tasks that looks like this:

Tasks Tracker

My next tab looks like this:

Generator

It uses a COUNTIF (COUNTIF(all cells in that day's column on the first sheet/photo except the header, "Company's name")) to determine how many tasks any company has within that day; that's the "RAW" number. The "PERCENT" divides the "RAW" number by how many total people they have in the company; it already works as needed. My goal is to type in a date into the cell beneath "Date" and have the cells to the right of "RAW" automatically inform me for that date typed in. The "PERCENT" already does its magic.

I just don't know how to have the "RAW" cells' formula reference all cells underneath a date on the first sheet/photo after typing that date into "Date" on the second sheet/photo. I was considering an offset, but I'm not sure how to offset a range from the formula of another cell: A2:1000 if the formula of another cell [Date] references A1, and have those RAW cells reference change to DC2:1000 if that Date cells value or reference changes to DC1.

Solution

  • Use MATCH to find the date in the row of dates and insert this into OFFSET, as the COLUMNS argument, to say how many COLUMNS to the right you need to move, to count over the correct range for the selected date.

    Say you had your selected date is in Sheet1!D2:

    Date selection field and where to enter formula

    And your companies' tasks in sheet2, with the dates from column B1 onwards, and company HHC spans rows A2:A7:

    Tasks in sheet2

    You want to find the column containing the selected date using:

    =MATCH(Sheet1!D2,Sheet2!1:1,0)-1) 'returns 9

    The adjustment of minus one is that dates actually start in column B not A in row 1.

    You know HHC spans Sheet2!A2:A7, in this example, and you now know you want to move 9 columns to the right of this to do your count.

    Using OFFSET and the columns argument (9) yields Range J2:J7

    OFFSET(Sheet2!A2:A7,,MATCH(Sheet1!D2,Sheet2!1:1,0)-1)

    Inserting this into your COUNTIF, as the source range, with "HHC" as the criteria:

    =COUNTIF(OFFSET(Sheet2!A2:A7,,MATCH(Sheet1!D2,Sheet2!1:1,0)-1),"HHC")

    This is then the formula that would go in cell Sheet1!C1 next to RAW.

    The same principles apply to your other companies. Define the start range for the company, use MATCH to determine the number of columns to OFFSET by, and then wrap it all in a COUNTIF.

    A match being found depends on:

    1. Date being present in search range;
    2. That dates are of the same datatype in both sheets (i.e. dates in both sheets or strings in both sheets).