Google-sheets – Google Sheets – Checking values for specific criteria based on another cell’s value

google sheets

I have some raw data and I'm looking to create a "dashboard" of sorts that would list out the learner and whether or not they've completed a learning plan based on their worksite.

In the "inputs" tab I've noted which learning plan (and thus which courses) is required per site. So, for example, if a learner's worksite is "BP" then they should either have completed the learning plan titled 'HC – Refresher Training – BP – V3 (Chinese Subtitles)' or 'HC – Refresher Training – BP – V3' since those are the only two learning plans that can be assigned at the "BP" worksite per the inputs tab.

I'm not really sure what steps to take to get to my end result, if that makes sense.

  • Is there a way to set up this spreadsheet so that I am able to list out the learners and the progress of the entire learning plan (based on the inputs sheet) for each individual learner?
  • I would also like this spreadsheet to be flexible. So, for example, maybe I'd like to display the progress of all learners with the worksite "BP" and list their progress with their current learning plan and a date (so that it can be sorted with the most recent dates listed first).

My mind is just as flustered as this question so please don't hesitate to ask follow up questions!

Quick Update: This formula sort of works

=IF(ISBLANK($G2),"",IF(AND(SUMPRODUCT(--($G2=Inputs!$J$2:$J$40))>0&SUMPRODUCT(--($A2=Inputs!$G$2:$G$40))>0&SUMPRODUCT(--($H2=Inputs!$H$2:$H$40))>0,$J2="Complete"),"Complete","Incomplete"))

G2 = worksite, A2 = learning plan, H2 = course title, J2 = training
status

The "inputs" reference is just a unique list so, essentially, the formula checks that the worksite, learning plan, etc, fall within values on the list (inputs tab) but the problem with this formula is it doesn't necessarily identify if one of those courses isn't complete. It's only checking to make sure those courses listed are on the list.

Best Answer

You want to create a "dashboard" of sorts that would list out the learner and whether or not they've completed a learning plan based on their worksite".

This answer addresses the aspect of generating a list of learners and the status of their learning plan. There are many ways that this analysis might be further refined, fine-tuned, made dynamic, charted, pivoted and so on. But your starting point is how to generate the list, to which I have added a couple of analysis options.

I trust that I have understood you correctly. If not, then please let me know.

Learning Plans

Your sheet "INPUT" contains two sets of data.

  • Columns A, B, C : a (master?) list of 74 x Activity Codes, Activity Titles, and Department codes
  • Columns E to I: a sub-set of 34 x Activity Codes and associated Learning Plans. The Activity Title is hard-coded, the Activity Code is a VLOOKUP from the master list, the Worksite is hard-coded, Department is a lookup from the master list, and the Learning Plan is hard-coded.

I created a new sheet "INPUTS_LPlans" to store the 34 x known learning plans (the equivalent of columns E to I of "INPUT".

The layout look like this:

LearningPlans

Training Records

Your sheet "Training_Records_Report_TEST-2020-12-28_13_06_05" contains 3797 x Staff Training Records.

A problem with this sheet is that while the data includes Employee details, Activity Code, Department and Worksite, it does not display the relevant Learning Plans.

I resolved this by adding a new Column N ("Learning Plan") which uses VLOOKUP to include the relevant Learning Plan for each row. The formula in Cell N3 is:

=ARRAYFORMULA(IFERROR(VLOOKUP(F3:F&K3:K, {INPUTS_LPlans!B2:B&INPUTS_LPlans!C2:C,INPUTS_LPlans!D2:E},3,0),"No Plan"))

In this case, the search key for VLOOKUP is a combination of two values ("F3:F&K3:K") - "F": the activity code, and K: the worksite. It is wrapped in IFERROR. Of the 3,797 records, 1,511 records showed that there was "No Plan" assigned. I have attributed this to the brevity of the Learning Plan data on "INPUT".

The revised Training Report looks like this.

TrainingRecords

List of Learners and status of Learning Plans

This is intended to go on a new sheet.

  • At Cell B1, use Data Validation to generate a list of Departments
  • At Cell B2, use Data Validation to generate a list of Training Status outcomes
  • At Cell A4, enter this query:

=query('Train_Records-2020'!A2:N,"select B,A,C,K,G,N,I,L where A is not null and K='"&B1&"' and L='"&B2&"' order by K, B, A format I 'dd-MMM-yyyy'")

  • edit the query to replace "Train_Records-2020" with the sheet name for your Training Records Report.
  • select a Department (Cell B1) AND a Status Outcome (Cell B2)

The sheet should look something like this:

Query

Related Topic