Return Max Date of Subject Based on Name in Column

jenkinjc

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?

Paul Newcome

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

Paul Newcome

A MAX/COLLECT would look something like this:

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

jenkinjc

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

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

jenkinjc

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

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?

jenkinjc

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

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

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))))

jenkinjc

=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

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

jenkinjc

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!!