I've had success using HTML to create a EXL2K cell value that is a formula, but displays as the result of that formula. I'm trying to take this one step further, and include the name of the worksheet in the Excel function parameters.
The Excel formula:

=SUMPRODUCT(--('Detail - Last 90 Days'!D10:D50000=(LEFT((T(INDIRECT(''A'' & ROW()))),(FIND('' /'',(T(INDIRECT(''A'' & ROW())))))-1))))  

I've gotten the formula into the HTML format, but I'm running into some problems with the quotation marks.

  
-*These are my defines
CLRTYP1/A200='<td align=left  x:num x:fmla="=SUMPRODUCT(--(';
CLRTYP2/A200='!D10:D50000=(LEFT((T(INDIRECT(''''A'''' & ROW()))),(FIND('''' /'''',(T(INDIRECT(''''A'''' & ROW())))))-1))))">0</td>';
CLRTYP/A500=CLRTYP1||''''||'Detail - Last 90 Days'||''''||CLRTYP2;

This gives me 2 single quotes around the A in the INDIRECT function instead of one double quote.

Does anyone know how to add the double quotes to to this define so that WebFOCUS won't think that the double quotes are the end of the text string?

Thank you.

This message has been edited. Last edited by: Kerry,

As far as I know, double-quotes mean nothing to WebFOCUS except for headings and footings. It's the single-quotes you have to worry about. I don't know the INDIRECT Excel function, but have you tried coding the double-quote around the A?

Coding the double quotes is what I'm asking how to do.

Consider the OVRLAY command...

Why not the obvious, - the double-quote character " ?

quote:

Originally posted by Francis Mariani:
Why not the obvious, - the double-quote character " ?

The double quote closes the string after the first INDIRECT(.

Therefore, I get the error:

HTML ERROR in Cell Formula :     =SUMPRODUCT(--('Detail - Last 90 Days'!D10:D50000=(LEFT((T(INDIRECT(

This really is a question of mixing single and double quotes. It has got nothing to do with webfocus, but everything with html. When you start a string in html with a single quote, the string ends when the next single quote is found. The same for double quotes. Since html does not care where which quote is used, you can exploit that.
If the function INDIRECT requires double quotes, then everything in the string that is defined for x:fmla has to be with double quotes, since you'd be forced to delimit the x:fmla string with single quotes.
It would then be something like:

CLRTYP1/A200='<td align=left  x:num x:fmla=''=SUMPRODUCT(--("Detail - Last 90 Days"';
CLRTYP2/A200='!D10:D50000=(LEFT((T(INDIRECT("A" & ROW()))),(FIND(" /",(T(INDIRECT("A" & ROW())))))-1))))''>0</td>';
CLRTYP/A500=CLRTYP1||CLRTYP2;

Hope this helps ...

Gamp: Well put. I'll keep that in mind myself.

I agree, it an HTML issue, and not a WebFOCUS issue. I can get this to work:

CLRTYP/A250='<td align=left x:num x:fmla="=SUM('||''''||'Detail - Last 90 Days'||''''||'!I10:I50)">0</td></tr>';

So I just need to figure out the right combination of quotes.

Thank you.

Here is a simplified version of my final solution:

CLRTYP/A400='<td align=left x:num x:fmla='||''''||'=SUM(CombinedDetail!I10:I50)'||''''||'>0</td>';

Once I replaced the double quote after "x:fmla=" with a single one ('||''''||'), and then noticed that the worksheet name only requires single quotes around it if the worksheet name is more than one word, the solution all came together!