CODEWITHSUNDEEP
phpphpexcel
OBAID
OBAID

Reputation: 1539

PHPExcel how to link a named range to a cell dropdown using setFormula1()

Hi guys i am using PHPExcel. how can i link a drop down cell to a named range column that is in different sheet previously i have linked cells to this dropdown like this

$objValidation->setFormula1('DropDownSheet!$A$1:$A$10');

DropDownSheet is a sheet name.

But now i have a named range countries_list i want to link this with dropdown using setFormula1() how can i do this i have tried this

$objValidation->setFormula1('=countries_list');
$objValidation->setFormula1('countries_list');

but its not working.

Upvotes: 1

Views: 1770

Answers (1)

Scott
Scott

Reputation: 706

Mark Baker is correct, here's my code that works fine ... first, in a loop, I set a list of countries in column A of sheet 2... in a loop that iterated over $counter:

$data_sheet = $objPHPExcel->createSheet();
$data_sheet->setTitle('Data');
$data_sheet->getCell('A'.$counter)->setValue($country["name"]);

Then I named that column 'countries':

$objPHPExcel->addNamedRange( 
    new PHPExcel_NamedRange(
        'countries', 
        $objPHPExcel->getSheet(1), 
        'A1:A'.($counter-1),
        false,
        NULL
    ) 
);

Then I wanted a pulldow list back on sheet 1 in the entire L column (well, for the first 1000 rows):

for($row = 2; $row<1002; $row++) {
    $objValidation = $objPHPExcel->getActiveSheet()->getCell("L".$row)->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('countries');
}

The last line names the list I named in the previous code block.

Upvotes: 1

Related Questions