Changing the way the IF() function is coded does work;
fordProdOrigin = workbook.createName();
fordProdOrigin.setNameName("fordProdOrigin");
fordProdOrigin.setRefersToFormula("'Data Sheet'!$B$3");
fordProdRange = workbook.createName();
fordProdRange.setNameName("fordProdRange");
fordProdRange.setRefersToFormula("'Data Sheet'!$B$3:$Z$3");
employeeConstraint = DVConstraint.createFormulaListConstraint(
"IF(A2=\"Ford\", " +
"IF(B2=\"Production\", OFFSET(fordProdOrigin, 0, 0, 1,
COUNTA(fordProdRange)), " +
"IF(B2=\"Design\", 'Data Sheet'!$B$4:$G$4, " +
"IF(B2=\"Marketing\", 'Data Sheet'!$B$5:$D$5, \"#Value\")))," +
"IF(A2=\"Toyota\", " +
"IF(B2=\"Planning\", 'Data Sheet'!$B$12:$E$12, " +
"IF(B2=\"Design\", 'Data Sheet'!$B$7:$E$7, " +
"IF(B2=\"Marketing-Europe\", 'Data Sheet'!$B$10:$E$10, " +
"IF(B2=\"Marketing-Americas\", 'Data Sheet'!$B$8:$F$8, " +
"IF(B2=\"Marketing-Australasia\", 'Data Sheet'!$B$9:$D$9, " +
"IF(B2=\"Marketing-Rest Of The World\", 'Data Sheet'!$B$11:$E$11, " +
"IF(B2=\"Production\", 'Data Sheet'!$B$13:$G$13, \"#Value\"))))))),
\"#Value\"))");
employeeValidation = new HSSFDataValidation(
employeeCellAddressList, employeeConstraint);
employeeValidation.setSuppressDropDownArrow(false);
sheet.addValidationData(employeeValidation);
In that piece of code, I have also shown how to use names and the OFFSET()
function to replace the familiar cell addresses. In short, everywhere you
see something like this - 'Data Sheet'!$B$11:$E$11 - it can be replaced with
the OFFSET() function. Simply define two named areas, one that points to the
first cell in the range, another that defines a contiguous series of cells
that will contain the data for that element and use them to create the
OFFSET() function and replace the cell reange address. Doing this will mean
that the Data Validation can be maintained using Excel rather than just POI.
It does however mean that you will have to declare quite a few names and I
am guessing there is a limit but do not know what this could be.
If you need any more info, just post to the list.
Yours
Mark B
nagineni wrote:
>
> Thanks Mark for the given idea of using dynamic drop downs.Yes,This way is
> one solution to the problem.
> But other issue is I've various levels of drop downs depends on each
> other.Here is an example as you said ,three drop downs of
> orginfo,branchinfo and empinfo.If I change first drop down ,the second
> should populate values,If I change second one ,third should populate it's
> values...like that I've many levels.
>
> Is this can be achieved in POI ?Sample code could be more helpful.Great
> thanks for the help.
>
> Regards,
> Naga.
>
> MSB wrote:
>>
>> Forgive me answering a question with a question please but is this even
>> possible using Excel itself? If it is, and you can find out how to
>> accomplish the same using Excel, then it may be possible to reproduce
>> this behaviour with POI.
>>
>> Just as an aside, rather than disabling items in the list, why not simply
>> change the list of items the user has to select from based upon certain
>> criteria. For example, it is possible to use formulae to determine the
>> items that appear in a drop down list. This formulae could make that
>> decision based upon some sort of criteria; the most regularly used being
>> the contents of another cell on the worksheet; as an example imagine that
>> you have a drop down list containing the names of al of the departmeents
>> within an organisation and another that you want to show the employees
>> but you only want to see those employed within the department selected in
>> the other dropdown list. One limitation is that drop down lists tend not
>> to be dynamic; by this I mean that you could select a dpeartment, then
>> select an employee then go back and select another department only to
>> find that the same employee was displayed whether or not they worked
>> within the newly selected department.
>>
>> Yours
>>
>> Mark B
>>
>>
>> nagineni wrote:
>>>
>>> Hi,
>>>
>>> Can we disable list box value in XLS using POI API ?I've created list
>>> box using HSSFDataValidation object and want to disable some of the
>>> items in the list box.Also it it possible to apply font/colors to the
>>> list box items ?
>>>
>>> Could any one let me know if we can achieve these features using POI
>>> API,sample code is really helpful.
>>>
>>> Thanks in advance.
>>>
>>
>>
>
>
--
View this message in context:
http://old.nabble.com/How-to-disable-list-box-values-in-XLS-using-POI-tp26357726p26454133.html
Sent from the POI - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
