Return Max Date of Subject Based on Name in Column

I designed a form for employees mimicking a training packet where the packet is made up of ten topics to be refreshed periodically. Each of the ten topics is submitted individually on the form therefore an employee can take them one at a time. For ease of status I've created a dashboard with a list of employees in a Column A and each of the ten topics heading Columns B-K. My goal is to return the most recent date for the specific topic for the specific employee on this dashboard.

I've poked around for examples with Max/Collect/Match/Index but all trials are yielding unparcelable or invalid results. May I get some help here or a different approach to the design?

Best Answer


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    A MAX/COLLECT would look something like this:

    =MAX(COLLECT({Date Range}, {Employee Range}, @cell = Employee@row, {Topic Range}, @cell = Topic@row))

  • I failed to add in a layer that may simplify this: At this point I have made it easier on supervisors without a dashboard to view employee training topic status by automating a move to a topic-specific grid, therefore I have ten grids.

    How does this look for the most recent date of Topic 1 training for "Joe Smith"? With this only the topic (and therefore reference sheet) will change in the summary for the dashboard.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It would look the same except you wouldn't need the range/criteria set regarding the Topic.

  • Thanks again for the attention Paul!

    I have a Master grid now that contains all the dates, employees, tasks in one sheet (after a form submission). I want the Summary grid will yield the last task completion date with static employee names in first column and static tasks across the top. I'm trying to collect Max Date of Employee Task Completion

    The formula you first provided yeilds UNPARSABLE for me in the summary sheet. Please know I'm a novice and excel based so maybe I'm not following your formula with @row and employee@row.

    I've attached a couple photos for further help of seeing the big picture. Please and thank you again

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    So do you have different sheets for each topic or one master sheet with all topics?

    What is the exact formula you are using that is populating the error?

  • Following up, Paul!

    From the examples above the larger chart (3 columns) is the Master sheet (fed from form) and the smaller example (2 columns) is the Summary. The summary columns will extend with each type of task in the columns to the right of "70% Isopropyl". I want to show. The employee will remain static with the goal showing the most recent completion date per task, per employee. The highlighted cell in my example charts here should report that Alex refreshed on 08/30/23 for 70% isopropyl.

    Here is my current unparseable formula:

    =MAX(COLLECT({Quality Quips [MASTER] Date Range}, {Quality Quips [MASTER] Employee Range}, @cell=Employee@row,{Quality Quips [MASTER] Task Range}, @cell="70% Isopropyl Alcohol"@row))))

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    You have no filled topic column on the row, so trim that part off:

    =MAX(COLLECT({Quality Quips [MASTER] Date Range}, {Quality Quips [MASTER] Employee Range}, @cell=Employee@row, {Quality Quips [MASTER] Task Range}, @cell="70% Isopropyl Alcohol"))

    The @row portion of the formula is looking at the column value (such as Employee) on the row. As you're trying to fill the value in the 70% Isopropyl Alcohol column, you don't want to reference this with @row as you don't have anything there for the formula to read.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You don't need the @row on the end of your last criteria, and you have too many closing parenthesis on the very end.

    =MAX(COLLECT({Quality Quips [MASTER] Date Range}, {Quality Quips [MASTER] Employee Range}, @cell=Employee@row,{Quality Quips [MASTER] Task Range}, @cell="70% Isopropyl Alcohol"@row))))

  • =MAX(COLLECT({Quality Quips [MASTER] Date Range}, {Quality Quips [MASTER] Employee Range}, @cell = Employee@row, {Quality Quips [MASTER] Task Range}, @cell="70% Isopropyl Alcohol"))

    Cell now yields: #Invalid Column Value.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    @jenkinjc Make sure you are putting the formula in a Date type column.

  • My man! That was it... I had it previously set to a Date type but had changed it trying things before the last edit to the formula. Thank you so much for all your help here!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!