Excel table with formulas not applying to the next rows

[marcwitteveen]

Hello,

I am using an excel template, in this template I have create a sheet names Rally with a table... the headers of these tables are in row 2 and have Code, Level, Round, Judge, Stream, Dog #, Name, Time and Score.

Trough code, on row 3 I have created a dropdown box for Code and lookup formulas for Level, Round and Judge.

However when I tab trough the cells on row 3 and it goes to row 4, I dont have these dropdown box and look up formulas anymore, while if I do it manual in excel I still have them.

The generated excel file: generated_file (21).xlsx

    namespace App\Services;

    use PhpOffice\PhpSpreadsheet\Spreadsheet;
    use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
    use PhpOffice\PhpSpreadsheet\Cell\DataValidation;
    use App\Models\Trials\TrialApplication;

    class RallySheetHandler extends ExcelSheetHandler
    {
        public function populate(array $params = [])
        {
            // Get the application and count the number of events
            $trialApplication = TrialApplication::with(['events.judge', 'events.eventLevel'])->find($params['application_id']);
            $numberOfEvents = $trialApplication->events->count();

            // Get the "Rally" sheet
            $sheet = $this->getSheetByNameOrCreate('Rally');

            // Create dropdown list for column A with "Code" values from "Application" sheet
            $dropdownFormula = 'Application!$B$13:$B' . (12 + $numberOfEvents );
            
            // Assuming the headers in the "Rally" sheet are in row 2
            $startRow = 3;

            $validation = $sheet->getCell('A' . $startRow)->getDataValidation();
            $validation->setType(DataValidation::TYPE_LIST);
            $validation->setErrorStyle(DataValidation::STYLE_STOP);
            $validation->setAllowBlank(false);
            $validation->setShowInputMessage(true);
            $validation->setShowErrorMessage(true);
            $validation->setShowDropDown(true);
            $validation->setFormula1($dropdownFormula);

            // Set formulas for Level, Round, and Judge based on the "Code" value
            $sheet->setCellValue('B' . $startRow, "=IFERROR(VLOOKUP(A$startRow, Application!B$12:F" . (12 + $numberOfEvents) . ", 2, FALSE), \"\")"); // Level
            $sheet->setCellValue('C' . $startRow, "=IFERROR(VLOOKUP(A$startRow, Application!B$12:F" . (12 + $numberOfEvents) . ", 3, FALSE), \"\")"); // Round
            $sheet->setCellValue('D' . $startRow, "=IFERROR(VLOOKUP(A$startRow, Application!B$12:F" . (12 + $numberOfEvents) . ", 4, FALSE), \"\")"); // Judge
        }
    }

[oleibman]

You need to add "Column Formulas" to your table:

        $table->getColumn('B')
            ->setColumnFormula(
                "=IFERROR(VLOOKUP(A$startRow, Application!B$12:F"
                . (12 + $numberOfEvents) . ", 2, FALSE), \"\")"
                );  // Level
        $table->getColumn('C')
            ->setColumnFormula(
                "=IFERROR(VLOOKUP(A$startRow, Application!B$12:F"
                . (12 + $numberOfEvents) . ", 3, FALSE), \"\")"
                ); // Round
        $table->getColumn('D')
            ->setColumnFormula(
                "=IFERROR(VLOOKUP(A$startRow, Application!B$12:F"
                . (12 + $numberOfEvents) . ", 4, FALSE), \"\")"
                ); // Judge