Errors adding data validation on data validation field

Topics: Developer Forum, User Forum
May 10, 2013 at 8:53 PM
I'm creating a list with Work Places like so (sheet 2 by name "dataValidation")
  $sql->query("select * from workPlaces order by workPlaceName ASC"); 
  while($sql->get_assoc()){                
    $sheet->setCellValue("A$currentRow","workPlace".$sql->get_acol('workPlaceId'));
    $sheet->setCellValue("B$currentRow",$sql->get_acol('workPlaceName'));
    $workPlaceIds[$sql->get_acol('workPlaceId')]=$currentRow;
    $currentRow++;
  }
  $sheet->setCellValue("A$currentRow","workPlace");
  $sheet->setCellValue("B$currentRow","Select Work Place");
then adding some comitees under each work place:
  $sql1->query("select * from comitees order by workPlaceId ASC");
  while($sql1->get_row()) {
    $id = $sql1->get_col();
    $wid = $sql1->get_col();
    if(!$owid) { $owid=$wid; }
    $name = $sql1->get_col();
    $sheet->setCellValue("A$currentRow",$id);
    $sheet->setCellValue("B$currentRow",$name);
    if($wid!=$owid) {
      $name = "workPlace$owid";
      $owid = $wid;
      $excel->addNamedRange(new PHPExcel_NamedRange($name,$sheet,"B{$comitees['start']}:B$currentRow"));
      $comitees['start']=$currentRow+1;
    }
    $currentRow++;
  }
So list with Work Places and Comitees (with named ranges) works fine, then adding Data Validation in 1st sheet named "Form":
  echo $range = "{$range['column']}{$range['start']}:{$range['column']}{$range['end']}"; // makes list from work Places
  $cell = "A1"
  $objValidation = $sheet->getCell($cell)->getDataValidation();
  $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
  $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
  $objValidation->setAllowBlank(true);
  $objValidation->setShowDropDown(true);
  $objValidation->setFormula1("dataValidation!$range");
  $sheet->setCellValue($cell,"Select Work Place");
up until this moment, everything works fine. but adding next data validation, Excel says that something is wrong, and removes every data validation.
  $cell = "A2";
  $objValidation = $sheet->getCell($cell)->getDataValidation();
  $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
  $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
  $objValidation->setAllowBlank(true);
  $objValidation->setShowDropDown(true);
  $objValidation->setFormula1("=INDIRECT(LOOKUP(A1;dataValidation!B1:dataValidation!B".count($workPlaceIds).";dataValidation!A1:dataValidation!A".count($workPlaceIds)."))");
but when i add formula:
  $objValidation->setFormula1('=INDIRECT("workPlace3"))');
it shows list wich is from Named range workPlace3

i tried to print that "not" working function and copy it in excel manually, and that didn't seem to show up errors.

Any ideas how to make it work ?
May 12, 2013 at 6:53 PM
Sorry, all sorted out! didn't know that setting formulas you have to use comma instead of semicolon.