PHPExcel: data validation does not work in .xls format

Question

I have an excel file with two sheets:

• Worksheet
• List - a list of items that will be displayed in the Worksheet as a list item.

Please see the images below:

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

 $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 cell range references on 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 an idea from http://phpexcel.codeplex.com/discussions/320393 :

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

Although this guy had another problem using named ranges.

Background: I think using

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

you explain using the given line between quotation marks as the value of the list, as described here: here (where you probably got this fragment in the first place) or here . But since you do not want to use elements of a fixed list, but are dynamically allocated, you must omit double quotation marks.