Cross referencing of excel sheets in jxl

[Vineet Shetty]

Hi Everyone,

I need some help in using the formula feature of JExcel. I need to cross reference two sheets in the same Excel file. The formula that I would have used if I were using Excel would have been

=IF(C2="Y",VLOOKUP(B2,Sheet1!$A$1:$C$2,2,0),"")

where Sheet1 is the name of my second sheet. Nw I want to get the same functionality using JExcel i.e. I want to write this formaula in one of the columns of my first sheet. I have tried doing this using the following lines of code:

public class Crossreference {

void writetext(WritableSheet sheet,int col,int row,String text)
{
Label label=new Label(col,row,text);
try {
sheet.addCell(label);
} catch (RowsExceededException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
}
}

public static void main(String[] args) throws RowsExceededException, WriteException {
WritableWorkbook workbook;
try {
Crossreference c=new Crossreference();
workbook = Workbook.createWorkbook(new File("C:\\Worksheet.xls"));
workbook.createSheet("Report", 0);
workbook.createSheet("Sheet1", 1);
WritableSheet excelsheet=workbook.getSheet(1);
c.writetext(excelsheet,0,0,"Name");
c.writetext(excelsheet,1,0,"Result");
c.writetext(excelsheet,0,1,"abc");
c.writetext(excelsheet,1,1,"This is the Result");
excelsheet=workbook.getSheet(0);
c.writetext(excelsheet,0,0,"Name");
c.writetext(excelsheet,1,0,"Yes/No");
c.writetext(excelsheet,2,0,"Implication");
c.writetext(excelsheet,0,1,"abc");
StringBuffer buf = new StringBuffer();
buf.append("IF(G2=\"Y\",VLOOKUP(A2,Sheet1!$A$1:$C$2,2,0),\"\")");
Formula f = new Formula(1,1,buf.toString());
excelsheet.addCell(f);
c.writetext(excelsheet,2,1,"");
workbook.write();
try {
workbook.close();
} catch (WriteException e) {
e.printStackTrace();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}

I am getting an error which says:

Warning: Could not find named cell Sheet1 when parsing formula IF(G2="Y",VLOOKUP(A2,Sheet1!$A$1:$C$2,2,0),"") in cell Report!B2

The JExcel homepage says that cross referencing of sheets is possible. So can anyone help me out with this problem.
Feel free to revert back in case any more details are needed.

Regards,
Vineet Shetty.

[AmooGoose Goose]

Hey I don't know if this helps but try using the the name of the sheet in ''. It worked for me. I found your question in another forum as well Also this guys looked like he knows what he was on about --> (http://www.google.com/support/forum/p/Google+Docs/thread?tid=1c1a648f0494cf1f&hl=en) webpage

Goodluck!!

My first post ever!! o.O