My structure is 2 separate tabs in the excel sheet. In one sheet, my \"Devops Tasks\" sheet, I have a status of \"COMPLETE\", and currently, everything besides 2 are set to \"INCOMPLETE\".

When I set the status to \"COMPLETE\" my formula populates this data over here to my other sheet which is labeled as \"Handover Report\" within my \"Completed DevOps Tasks This Shift\":

Right now though, it only populates as #NAME? - What exactly am I doing wrong here in this formula?

=@IF(COUNTIF('DevOps Tasks'!C[6],\"COMPLETE\")>=4,INDEX('DevOps Tasks'!C[2],@AGGREGATE(15,6,@ROW('DevOps Tasks'!C[6])/(@'DevOps Tasks'!C[6]=\"COMPLETE\"),4)),\"\")

As a side note, but maybe not entirely relevant - one way I'm auto-populating is through what I do within JS:

    const completedTasksTable = handoverSheet.addTable({
        name: 'CompletedTasks',
        ref: 'A' + (handoverSheet.rowCount + 1),
        columns: [
            { name: 'ID', filterButton: true, width: 15 },
            { name: 'Title', filterButton: false, width: 50 },
            { name: 'Completed By', filterButton: true, width: 25 },
            { name: 'Sprint', filterButton: true, width: 20 }
        ],
        rows: Array(10).fill().map((_, index) => [
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!C:C,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` },
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!D:D,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` },
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!B:B,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` },
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!A:A,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` }
        ])
    });


    const completedTasksHeaderRow = handoverSheet.getRow(completedTasksTable.headerRow?.firstRow || +1);
    if (completedTasksHeaderRow) {
        completedTasksHeaderRow.font = { bold: true };
        completedTasksHeaderRow.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFF0F5FF' }
        };
    }
    
    completedTasksRow.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FF1E4D8C' }
    };
    completedTasksRow.font = { color: { argb: 'FFFFFFFF' }, bold: true }; 

My structure is 2 separate tabs in the excel sheet. In one sheet, my \"Devops Tasks\" sheet, I have a status of \"COMPLETE\", and currently, everything besides 2 are set to \"INCOMPLETE\".

When I set the status to \"COMPLETE\" my formula populates this data over here to my other sheet which is labeled as \"Handover Report\" within my \"Completed DevOps Tasks This Shift\":

Right now though, it only populates as #NAME? - What exactly am I doing wrong here in this formula?

=@IF(COUNTIF('DevOps Tasks'!C[6],\"COMPLETE\")>=4,INDEX('DevOps Tasks'!C[2],@AGGREGATE(15,6,@ROW('DevOps Tasks'!C[6])/(@'DevOps Tasks'!C[6]=\"COMPLETE\"),4)),\"\")

As a side note, but maybe not entirely relevant - one way I'm auto-populating is through what I do within JS:

    const completedTasksTable = handoverSheet.addTable({
        name: 'CompletedTasks',
        ref: 'A' + (handoverSheet.rowCount + 1),
        columns: [
            { name: 'ID', filterButton: true, width: 15 },
            { name: 'Title', filterButton: false, width: 50 },
            { name: 'Completed By', filterButton: true, width: 25 },
            { name: 'Sprint', filterButton: true, width: 20 }
        ],
        rows: Array(10).fill().map((_, index) => [
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!C:C,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` },
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!D:D,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` },
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!B:B,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` },
            { formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!A:A,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` }
        ])
    });


    const completedTasksHeaderRow = handoverSheet.getRow(completedTasksTable.headerRow?.firstRow || +1);
    if (completedTasksHeaderRow) {
        completedTasksHeaderRow.font = { bold: true };
        completedTasksHeaderRow.fill = {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: 'FFF0F5FF' }
        };
    }
    
    completedTasksRow.fill = {
        type: 'pattern',
        pattern: 'solid',
        fgColor: { argb: 'FF1E4D8C' }
    };
    completedTasksRow.font = { color: { argb: 'FFFFFFFF' }, bold: true }; 

Seems incorrect use of @ symbol, the @ symbol is used for structured references in Excel tables. If you are not using tables, remove it from your formula.

Assuming you want to count the number of \"COMPLETE\" statuses and then get the data from the corresponding row, if yes, please:

=IF(COUNTIF('DevOps Tasks'!C:C,\"COMPLETE\")>=4, INDEX('DevOps Tasks'!B:B, AGGREGATE(15, 6, ROW('DevOps Tasks'!C:C)/('DevOps Tasks'!C:C=\"COMPLETE\"), 4)),\"\")\n

Make sure your formulae in JavaScript

{ formula: `=IF(COUNTIF('DevOps Tasks'!H:H,\"COMPLETE\")>=${index + 1},INDEX('DevOps Tasks'!C:C,AGGREGATE(15,6,ROW('DevOps Tasks'!H:H)/('DevOps Tasks'!H:H=\"COMPLETE\"),${index + 1})),\"\")` }\n

Why not you use FILTER() function?

Can you show me with how this formula fits in using FILTER() in a working condition? I already attempted this with FILTER in previous iterations:

=IFERROR(FILTER(]'DevOps Tasks\\'!C:C,\\'DevOps Tasks\\'!H:H=\"COMPLETE\"),\"\")'
Same repeating with !D, !B, !A with no success.

Can you please attach a sample with sample input data and showing your desired output? You may also share the file with OneDrive, Google-Drive or Dropbox.

That formula is in R1C1 format, so it's a little difficult to troubleshoot via a screenshot. Also what's the expected output on your Handover report?

Here are some more screenshots:

Below is from my DevOps task sheet2:

Photo 1

Photo 2

Below is from by Handover Report sheet 1:

The expected behavior is that I set the status to complete via drop down arrow in DevOps Tasks:

Then, after setting to complete, the ID, Title, who it is completed by or \"assigned to\" and which sprint it is from accurately populates here:

Whereas right now, setting anything to complete at any point seems to populate something, and that something only being this \"#NAME?\".