Create dependent Dropdown list using PHPExcel

When we create Dropdown list for Country and City. We can’t easly find out corrosponding city for the country. To make it easy excel have a function called Indirect. Basically returns the selected value. hence the similarly named ranges. so i select “UK”, it loads the named range called “UK” which has the UK cities. To use this function we create dependent Dropdown list.

$objPHPExcel->getSheetByName('Country')->SetCellValue("A1", "UK");
    $objPHPExcel->getSheetByName('Country')->SetCellValue("A2", "USA");

    $objPHPExcel->addNamedRange( 
        new PHPExcel_NamedRange(
            'country', 
            $objPHPExcel->getSheetByName('Country'), 
            'A1:A2'
        ) 
    );

$objPHPExcel->getSheetByName('Country')->SetCellValue("B1", "London");
    $objPHPExcel->getSheetByName('Country')->SetCellValue("B2", "Birmingham");
    $objPHPExcel->getSheetByName('Country')->SetCellValue("B3", "Leeds");
    $objPHPExcel->addNamedRange( 
        new PHPExcel_NamedRange(
            'UK', 
            $objPHPExcel->getSheetByName('Country'), 
            'B1:B3'
        ) 
    );

$objPHPExcel->getSheetByName('Country')->SetCellValue("C1", "Atlanta");
    $objPHPExcel->getSheetByName('Country')->SetCellValue("C2", "New York");
    $objPHPExcel->getSheetByName('Country')->SetCellValue("C3", "Los Angeles");
    $objPHPExcel->addNamedRange( 
        new PHPExcel_NamedRange(
            'USA', 
            $objPHPExcel->getSheetByName('Country'), 
            'C1:C3'
        ) 
    );

Now to using named ranges load up the first dropdown to select the countries.

$objValidation = $objPHPExcel->getActiveSheet()->getCell('A1')->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->setFormula1("=country"); 

$objValidation = $objPHPExcel->getActiveSheet()->getCell('B1')->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->setFormula1('=INDIRECT($A$1)');