I have a Gsheet for a Service Schedule I would like to keep track of who last completed…
- User
I have a Gsheet for a Service Schedule I would like to keep track of who last completed a Job. In H2:H24 I have Technician 1's Names In I2:I24 I have technician 2's Names in K2:K24 I would like to keep the last input into H2:I24 in J2:J24 I have a Status based on =IF(F2>=D2, "Completed", "Due") D2 being the allocated Hrs/Mnth and F2 being the hours expended
- Assistant
To track the last technician(s) who completed a job in your Google Sheet, you can use a combination of Google Sheets formulas and possibly Google Apps Script (if you want to automate the process of recording the last input). Below, I'll outline a solution that uses formulas to achieve this, and if needed, I can also provide a script-based solution for more advanced automation.
Goal:
- In column
J(J2:J24), you want to display the name(s) of the last technician(s) who worked on a job, based on the latest input in columnsH(Technician 1) andI(Technician 2). - The status in column
K(K2:K24) is determined by the formula=IF(F2>=D2, "Completed", "Due").
Solution Using Formulas:
To track the last technician(s) who worked on a job, you can use a formula in column
Jto concatenate the names of the technicians inHandIif the job is marked as "Completed" in columnK. Here's how you can do it:-
Add a Formula in J2: In cell
J2, enter the following formula:=IF(K2="Completed", TRIM(CONCATENATE(H2, IF(AND(H2<>"", I2<>""), " & ", ""), I2)), "")This formula does the following:
- Checks if the status in
K2is "Completed". - If "Completed", it concatenates the names in
H2(Technician 1) andI2(Technician 2) with an " & " separator if both names are present. - If only one name is present, it shows just that name.
- If the status is not "Completed", it leaves the cell blank (
"").
- Checks if the status in
-
Copy the Formula Down: Copy the formula from
J2down toJ24to apply it to all rows.
Explanation of the Formula:
K2="Completed": Checks if the job is marked as completed.TRIM(CONCATENATE(H2, IF(AND(H2<>"", I2<>""), " & ", ""), I2)): Combines the names inH2andI2with an " & " separator if both are non-empty. TheTRIMfunction ensures no extra spaces are included.IF(K2="Completed", ..., ""): Ensures that the technician names are only shown if the job is completed; otherwise, the cell remains blank.
Example Output:
H (Tech 1) I (Tech 2) K (Status) J (Last Tech) John Completed John Alice Bob Completed Alice & Bob Charlie Due David Eve Completed David & Eve Limitations of the Formula-Based Approach:
- The formula only shows the current names in
HandIwhen the job is marked as "Completed". It does not "remember" the last names entered if the names inHorIare later changed or cleared. - If you need to permanently record the names of the technicians who last worked on the job (even if
HorIis later modified), you will need to use Google Apps Script to "freeze" the names in columnJat the time the job is marked as "Completed".
Advanced Solution Using Google Apps Script (Optional):
If you want to permanently record the last technician names in column
Jwhen the status changes to "Completed" (and prevent overwriting ifHorIis later changed), you can use Google Apps Script. Here's how:-
Open the Script Editor:
- In your Google Sheet, go to
Extensions > Apps Script.
- In your Google Sheet, go to
-
Add the Following Script:
function onEdit(e) { var sheet = e.source.getActiveSheet(); var range = e.range; var row = range.getRow(); var col = range.getColumn(); // Check if the edit is in the "Status" column (K, column 11) and within rows 2-24 if (col === 11 && row >= 2 && row <= 24) { var status = range.getValue(); var tech1 = sheet.getRange(row, 8).getValue(); // Column H (Tech 1) var tech2 = sheet.getRange(row, 9).getValue(); // Column I (Tech 2) var lastTechCell = sheet.getRange(row, 10); // Column J (Last Tech) // If status is "Completed", record the technician names in column J if (status === "Completed") { var lastTech = ""; if (tech1 && tech2) { lastTech = tech1 + " & " + tech2; } else if (tech1) { lastTech = tech1; } else if (tech2) { lastTech = tech2; } lastTechCell.setValue(lastTech); } } } -
Save and Authorize the Script:
- Save the script and grant the necessary permissions when prompted.
-
How It Works:
- The script triggers automatically whenever an edit is made in the Google Sheet (
onEdittrigger). - It checks if the edit is in column
K(Status) and within rows 2-24. - If the status is changed to "Completed", it reads the names in
H(Tech 1) andI(Tech 2) and writes them toJ(Last Tech) in the same row. - Once the names are written to
J, they will not change even ifHorIis later modified.
- The script triggers automatically whenever an edit is made in the Google Sheet (
Notes:
- If you want to clear the names in
Jwhen the status changes back to "Due", you can modify the script to include anelsecondition to clear thelastTechCellvalue. - The script assumes that the status in column
Kis manually updated or updated via the formula=IF(F2>=D2, "Completed", "Due"). If the status is formula-based, the script will not trigger automatically becauseonEditonly triggers for manual edits. In that case, you would need a different approach, such as a time-driven trigger or a custom function to periodically check the status.
Which Solution to Use?
- Use the formula-based solution if you are okay with the names in
Jbeing dynamically updated based on the current values inHandI. - Use the Google Apps Script solution if you need to permanently record the names in
Jat the time the job is marked as "Completed" and prevent them from being overwritten.
Let me know if you need further clarification or help implementing either solution!
- In column
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.