Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

[Poddys]

I struggle with Java including the HSSF routines provided by Scott Klements, and I wondered if someone might be able to help me with a formula problem.

I have an RPG program that creates a workbook with 2 sheets, the second sheet being blank - data will be manually pasted in by the user later - but I don't think that is the problem.

The program works, apart from a formula that I am trying to include in one of the columns, the formula referencing cells in the 2nd sheet via VLOOKUP.

When I try to run the program I get:

The call to HSSF_FORMU ended in error

I know that the formula has been coded correctly (with all the concatenations) because if I output the value as text into the cell instead of as a formula, and then in the spreadsheet I prefix it with "=", it brings up the correct value. However, if I try to use this value to create a formula in the cell it blows up.

The formula value is:

IF(ISNA(VLOOKUP(A6,'Last Month'!A:G,7,FALSE)=TRUE),"",VLOOKUP(A6,'Last Month'!A:G,7,FALSE))

Basically this takes the value in column A on the current row, looks up the value in column A in the 2nd sheet ('Last Month'), and returns the value in column G.

The RPG/Free code that generates this is:

cellid = 'A' + %char(rowCount+1);
sheetname = '''Last Month''';
celldata = 'IF(ISNA(VLOOKUP('+cellid+
','+sheetname+'!A:G,7,FALSE)=TRUE),"",VLOOKUP('+
cellid+','+sheetname+'!A:G,7,FALSE))';
hssf_formula(row: 19 : celldata: Numeric);

If I omit the single quote around the sheet name, the program works but the formula doesn't, by the way.

I have been tearing my hair out with this, any help would be much appreciated.

[Poddys]

Re: Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

Sometimes you need to scream until you are blue in the face and ask for help before the ligh goes on.

I renamed the 2nd sheet to "LastMonth" to eliminate the space, and now I can eliminate the quotes around it.
This I hope would fix the problem, but alas no

My formula now is:

IF(ISNA(VLOOKUP(A6,LastMonth!A:G,7,FALSE)=TRUE),"",VLOOKUP(A6,LastMonth!A:G,7,FALSE))

[gwilburn]

Re: Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

I think your issue is the quotes... I've ran into this when trying to run commands w/in rpg with quotes....

Try something like this

 *                                             
D q               s              1a   inz('''')
 *                                             

sheetname =  q + 'LastMonth' + q;

Hopefully it's as simple as that... normally things that cause you to pull your hair out are simple.

[Poddys]

Re: Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

Thanks for the help, but alas it made no difference.

The formula text works if you set a cell to the concatenated value, then in Excel edit it to put the "=" in front, but if you try to create it as a formula using hssf_formula it blows up.

I am wondering if it's the range (A:G) in the VLOOKUP that is the problem.

It obviously works in Excel. It's really frustrating that the Java routine can't accept what you enter for a cell.

[Poddys]

Re: Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

Ok, another step forward, another step back...

I wondered if it was the VLOOKUP range that was causing the problem, so instead of coding "A:G" I coded "A5:G55" as the range.

Now the spreadsheet is generated without my RPG program failing, however, the formula is showing as:

=IF(ISNA(VLOOKUP(A6,#REF!A5:G55,7,FALSE)=TRUE),"",VLOOKUP(A6,#REF!A5:G55,7,FALSE))

I am wondering if references to the 2nd sheet are being checked, and since this is not created until later in the job, they are being rejected and changed, which seems silly since it's Excel that is supposed to process the formulas.

I took the advice to output quotes around my sheet name, and it doesn't seem to make a difference if I do or not, I just get #REF

The formula is supposed to be :

=IF(ISNA(VLOOKUP(A5,LastMonth!A5:G55,7,FALSE)=TRUE ),"",VLOOKUP(A5,LastMonth!A5:G55,7,FALSE))

I'm just going crazy here, closer but no closer...

[Poddys]

Re: Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

In the absence of responses I am sort of thinking out loud as to how I might resolve this problem.

One solution would be to save the data from the previous run (an iSeries physical file) and to just retrieve this, eliminating the need for the previous month sheet to be copied/pasted in for the formula to work. I didn't do this originally since I didn't realise adding a formula that linked to cells in another sheet would be a complete pain in the proverbial...

Another solution at this point might be to create the 2 sheets empty, save then re-open the workbook, and to write out the contents of the first sheet as I am already doing. Assuming that the problem I am having is because the 2nd sheet doesn't yet exist when I create the formula, it ought to work. However, if it doesn't, this is more work/time down the plughole, and at the end of the day the user still has to paste in the data from the previous month.

Which way to turn..... I am slowly thinking that just recording the data for previous months might be the way.

[tomholden]

Re: Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

just a thought...create the blank worksheets, populate the 2nd one first then populate the 1st worksheet.

[Poddys]

Re: Problem: Creating Excel Spreadsheet With Formula Referencing Sheet

Thanks Tom.

I thought about that, but really want the main data in the first sheet. If I knew that creating both sheets, then going back and populating the first would work, I would do that, but the more I thought about this problem late yesterday, the more I thought about another solution.

So, I am currently updating the job, creating a summary file of Last Month's data when the job is run at monthend, and I am going to retrieve this the next month, so I not only can eliminate the second sheet, but this column will be hard coded instead of a formula.

I really didn't want to have to go to all this effort, but trying to get the formula to work with references to a separate sheet is taking far too long to resolve, and each step forward has so far resulted in another step backwards.

HSSF (Horrible SpreadSheet Format) certainly does live up to it's name...