I am using a laravel package "Maatwebsite" to export my data into a xls format.
As my data are complex and requires some complex formula as well.
I need to CONCATENATE two cells using LOOKUP formula and the formula looks like this
=IF(B10<>"",CONCATENATE(LOOKUP(2,1/($A$2:A10<>""),$A$2:A10),"_",B10),"")
But the problem is when ever i try to export into the xls the xls file only contains static value but not with formula.
What is want is when ever there is a change in A10 and B10 it should reflect it in C10 as well. But It doesn't .
My Code looks like this
$formula = '=IF(B10<>"",CONCATENATE(LOOKUP(2,1/($A$2:A10<>""),$A$2:A10),"_",B10),"")';
$sheet->setCellValueExplicit("C10", $formula, DataType::TYPE_FORMULA);
$sheet is from "AfterSheet" events.
but when i try using simple formula like
$formula = '=SUM(A10,B10)';
$sheet->setCellValueExplicit("C10", $formula, DataType::TYPE_FORMULA);
It works perfectly as expected.
For this to work properly, you need to be generating an XLSX file, not a legacy XLS file.