Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Include Worksheet Name in HTML Formula for EXL2K

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Include Worksheet Name in HTML Formula for EXL2K
 Login/Join
 
Platinum Member
posted
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,


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Expert
posted Hide Post
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?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
Coding the double quotes is what I'm asking how to do.


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Expert
posted Hide Post
Consider the OVRLAY command...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Expert
posted Hide Post
Why not the obvious, - the double-quote character " ?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Platinum Member
posted Hide Post
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(


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Virtuoso
posted Hide Post
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

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Expert
posted Hide Post
Gamp: Well put. I'll keep that in mind myself.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Platinum Member
posted Hide Post
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.


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
Platinum Member
posted Hide Post
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!


Production - 7.6.4
Sandbox - 7.6.4
 
Posts: 241 | Location: Bethesda, MD | Registered: August 14, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Include Worksheet Name in HTML Formula for EXL2K

Copyright © 1996-2020 Information Builders