If Target.Row = 1 And Target.Column = 1 Then
Dim sourceRow As Integer
sourceRow = Target(1, 1).Value

For Each littleCell In Worksheets(sourceSheet).Rows(sourceRow).Cells
    Worksheets(destinationSheet).Rows(destinationRow).Cells(littleCell.Column).Value = littleCell.Value
next

HootTheOwl fucked around with this message at 14:40 on Jan 18, 2023

Harminoff fucked around with this message at 23:11 on Jan 24, 2023

• Use a javascript array to hold your variables rather than another sheet.
  
• Try to do all your updating at once, rather than each cell one at a time. Apps script makes a call for each cell read or write, so if you can bundle it into a single setValues() instead of multiple setValue() it will be much quicker.
  
• Use a custom number format for your items so they are numbers rather than strings: Format > Number > Custom Number Format > 0 "(hours)" / 0 "(days)" . This will make working with the cell data much easier.
  

const boats = [
  {
    "currentHours": "C3",
    "currentDate": "D3",
    "lastRecordedHours": "C4",
    "lastRecordedDate": "D4",
    "items": "B8:D53"
  },
  {
    "currentHours": "G3",
    "currentDate": "H3",
    "lastRecordedHours": "G4",
    "lastRecordedDate": "H4",
    "items": "F8:H53"
  },
];

// easy way to log errors as the execution log is unreliable for onEdit 
function log(spreadsheet, message) {
  const logSheet = spreadsheet.getSheetByName("Errors") || spreadsheet.insertSheet("Errors");
  logSheet.appendRow([new Date(), message] );
}

function onEdit(e) {
  const range = e.range;
  const editedCell = range.getA1Notation();
  const sheet = range.getSheet();

  try {
    
    boats.forEach(boat => {
      if(editedCell === boat.currentDate) {
        //calc deltas
        const currHours = sheet.getRange(boat.currentHours).getValue();
        const prevHours = sheet.getRange(boat.lastRecordedHours).getValue();
        const hours = currHours - prevHours;

        const currDate = sheet.getRange(boat.currentDate).getValue();
        const prevDate = sheet.getRange(boat.lastRecordedDate).getValue();
        const days = (currDate - prevDate) / 86400000; // milliseconds in 1 day
        
        // update last recorded fields and reset current fields
        sheet.getRange(boat.lastRecordedHours).setValue(currHours);
        sheet.getRange(boat.lastRecordedDate).setValue(currDate);
        sheet.getRange(boat.currentHours).setValue("");
        sheet.getRange(boat.currentDate).setValue("");

        // update the cells in the boats items.
        // collect all the values and set them all at once.
        const itemRange = sheet.getRange(boat.items);        
        const values = itemRange.getValues();
        
        for(let r = 0; r < values.length;r+=2) {
          for(let c = 0; c < values[r].length; c+=1) {
            values[r][c] += hours;
            values[r+1][c] += days;
          }
        }
        itemRange.setValues(values);
      }

    });
  } 
  catch (error) {
    log(e.range.getSheet().getParent(), error.message);
  }
}

HootTheOwl fucked around with this message at 13:32 on Apr 28, 2023

=COUNTIFS(B:B, "pass", A:A, ">0") + COUNTIFS(C:C, "pass", A:A, ">0")

HootTheOwl fucked around with this message at 17:58 on May 4, 2023