php - PHPExcel : Data validation not working in .xls format -

i have excel file 2 sheet:

1. worksheet;
2. list- list of items display in worksheet sheet list item.

please see below images:

i want generate using phpexcel library. have tried not getting expected results. see 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 office 2007 xlsx, generated using php classes.")                              ->setkeywords("office 2007 openxml php")                              ->setcategory("test result file");   // create 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 not in list.'); $objvalidation->setprompttitle('pick list'); $objvalidation->setprompt('please pick value drop-down list.'); $objvalidation->setformula1('"$list.$a$1:$a$10"');  // make sure put list items between " , "  !!!  $objphpexcel->createsheet(); $objphpexcel->setactivesheetindex(1); $objphpexcel->getactivesheet()->settitle('list');  ($i = 1; $i <= 10; $i++) {     $objphpexcel->getactivesheet()->setcellvalue("a{$i}", "list item {$i}"); } // set active sheet index first sheet, excel opens 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;  

i think correct syntax referencing ranges of cells in sheet is:

list!$a$1:$a$10 

so should try:

$objvalidation->setformula1('list!$a$1:$a$10'); // tested it, worked me 

got idea http://phpexcel.codeplex.com/discussions/320393:

->setformula1("worksheet!a1:{$endcell}1");// work....

although guy had problem using named ranges.

background: think with:

$objvalidation->setformula1('"$list.$a$1:$a$10"'); 

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