You're right, you can't reference another sheet in a DataValidation formula.
But you can't do that in Excel neither...
Just define a named range in Sheet1 on cells A1:A4 and use it instead
of the sheet reference :
String strFormula = "MyNamedRange";
This will be accepted either in Excel and in POI.
Anyway, this was not working in POI until some recent changes, be sure
to use a recent SVN build or get the source from 3.1 and apply the
changes I've posted recently about HSSFDataValidation.
Hope it helps...
Pierre
On Tue, Sep 9, 2008 at 8:24 PM, krishnanand thiyadath
<[EMAIL PROTECTED]> wrote:
> Hi Pierre,
>
> Thanks for your reply.
>
> I am using the following code and adding data validation to a cell. The cell
> to which i am adding the data validation is in Sheet2. And the named range
> that i have created is in Sheet1
>
> String strFormula = "Sheet1!$A$1:$A$4"; // cells that are in different sheet
> is referenced
> data_validation = new
> HSSFDataValidation((short)(start_row),(short)0,(short)(start_row),(short)0);
>
> data_validation.setDataValidationType(HSSFDataValidation.DATA_TYPE_LIST);
> data_validation.setFirstFormula(strFormula);
> data_validation.setSecondFormula(null);
> data_validation.setSurppressDropDownArrow(false);
> data_validation.createErrorBox("Invalid input !", "Something is wrong ;
> check condition !");
> data_validation.createPromptBox("Hi , dear user !", "So , you just
> selected me ! Thanks !");
> fSheet.addValidationData(data_validation);
>
> and then finally writing it to new file.
>
> When i try to open the file, it shows me a error message "Data has been
> lost".
> And Finally when the file is opened, i dont see any data in the cell, to
> which i have added the data validation.
>
> Note: Instead of referencing the cells from different sheet, if i reference
> from the same sheet, then there is no problem.
>
> If you need any clarification or any more information, please let me know.
>
> thanks in advance.
>
>
> On Tue, Sep 9, 2008 at 10:34 PM, Pierre Lavignotte <
> [EMAIL PROTECTED]> wrote:
>
>> Hi,
>>
>> you can use the named range in formulas or in Data Validation too.
>>
>> Search for recent messages with HSSFDataValidation and you will find
>> some exemple code.
>> Come back if you need more information.
>>
>> Pierre
>>
>> On Tue, Sep 9, 2008 at 1:33 PM, krishnanand thiyadath
>> <[EMAIL PROTECTED]> wrote:
>> > Hi Guys,
>> >
>> > I have created a named range using the following code
>> >
>> > HSSFWorkbook wb = new HSSFWorkbook();
>> >
>> > HSSFName name = wb.createName();
>> > name.setNameName("test");
>> > name.setReference("sheet1!$A$1:$A$4");
>> >
>> > But i dont know, how to reference this named range in other cell.
>> >
>> > For example in MS Excel, if i create a named range, i can use it in
>> > DataValidation, by selecting the named range name as source and type as
>> > LIST.
>> >
>> > Can any one tell me, how we can use the named range using POI.
>> >
>> > --
>> > with thanks,
>> > krishnanand.
>> >
>>
>>
>>
>> --
>> Cordialement,
>> Pierre Lavignotte
>> Ingénieur Conception & Développement
>> http://pierre.lavignotte.googlepages.com
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [EMAIL PROTECTED]
>> For additional commands, e-mail: [EMAIL PROTECTED]
>>
>>
>
>
> --
> with thanks,
> krishnanand.
>
--
Cordialement,
Pierre Lavignotte
Ingénieur Conception & Développement
http://pierre.lavignotte.googlepages.com
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
