PHPExcel : Data validation not working in .xls format

Question

I have a excel file with two sheet:

1. Worksheet;
2. List- list of items which will display in Worksheet sheet as a list item.

Please see the below images:

I want to generate this using PHPExcel library. I have tried but not getting the expected results. See my below code :

$objPHPExcel = new PHPExcel();
// Set document properties
$objPHPExcel->getProperties()->setCreator("Soumya Biswas")
                             ->setLastModifiedBy("Soumya Biswas")
                             ->setTitle("Office 2007 XLSX Test Document")
                             ->setSubject("Office 2007 XLSX Test Document")
                             ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                             ->setKeywords("office 2007 openxml php")
                             ->setCategory("Test result file");


// Create a first sheet
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A5', "List");


// Set data validation
$objValidation = $objPHPExcel->getActiveSheet()->getCell('B5')->getDataValidation();
$objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('Input error');
$objValidation->setError('Value is not in list.');
$objValidation->setPromptTitle('Pick from list');
$objValidation->setPrompt('Please pick a value from the drop-down list.');
$objValidation->setFormula1('"$List.$A$1:$A$10"');  // Make sure to put the list items between " and "  !!!

$objPHPExcel->createSheet();
$objPHPExcel->setActiveSheetIndex(1);
$objPHPExcel->getActiveSheet()->setTitle('List');

for ($i = 1; $i <= 10; $i++) {
    $objPHPExcel->getActiveSheet()->setCellValue("A{$i}", "List Item {$i}");
}
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);




header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="data-validation.xls"');
header('Cache-Control: max-age=0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit; 

Answer 1

I think the correct syntax for referencing ranges of cells in another sheet is:

List!$A$1:$A$10

So you should try:

$objValidation->setFormula1('List!$A$1:$A$10'); // tested it, worked for me

Got the idea from http://phpexcel.codeplex.com/discussions/320393:

->setFormula1("Worksheet!A1:{$endCell}1");// work....

Although this guy had another problem with using named ranges.

Background: I think with:

$objValidation->setFormula1('"$List.$A$1:$A$10"');

you're explicity using the given string between the quotation marks as the list value as explained here: here (where you probably got this snippet in the first place) or here. But since you don't want to use fixed list items but dynamically referred ones, you should omit the double quotation marks.