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     [CLOSED] Excel Formula with multiple tabs

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Excel Formula with multiple tabs
 Login/Join
 
Gold member
posted
Hi,

I would like to create the multile sheet excel book and write the formulas and populate values in a worksheet referencing cells from other sheets. Is there a way to do it? Please share some sample code.

Thank You
Sandhya

This message has been edited. Last edited by: Kerry,
 
Posts: 53 | Registered: December 06, 2011Report This Post
Gold member
posted Hide Post
I don't know how many tab you will need, but here is a simple one with the Excel Template having three tabs.

The first tab I have mapped to the cells of the second tab.

You need to save the Excel Template as a ".mht" file.

 
APP FI OUTPUT1 DISK output1.mht

TABLE FILE CAR
SUM
     CAR.BODY.DEALER_COST
     COMPUTE DIFF/D13 = (RETAIL_COST - DEALER_COST);
BY  LOWEST CAR.ORIGIN.COUNTRY
BY  LOWEST CAR.COMP.CAR
BY  LOWEST CAR.CARREC.MODEL
ON TABLE HOLD AS OUTPUT1 FORMAT EXL2K FORMULA TEMPLATE 'CAR_TESTmht' SHEETNUMBER 2
END
-RUN

TABLE FILE CAR
SUM
     CAR.BODY.SALES
     COMPUTE DIFF/D13 = (RETAIL_COST - DEALER_COST);
BY  LOWEST CAR.ORIGIN.COUNTRY
BY  LOWEST CAR.COMP.CAR
BY  LOWEST CAR.CARREC.MODEL
ON TABLE PCHOLD FORMAT EXL2K FORMULA TEMPLATE 'OUTPUT1' SHEETNUMBER 3
END
-RUN

 


WF 7.6.10, Windows, PDF, Excel
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Report This Post
Expert
posted Hide Post
There are also techniques to embed a formula ini the excel spreadsheet, its a little tricky, but can work.

The following example could be expanded to get a value from another sheet if it existed.

e.g.
DEFINE FILE CAR
COL0/A25= 'Clearance/Type';
COL1/A25= 'Count';
COL2/A25= 'Average Cycle Time (Days)';
END
TABLE FILE CAR
HEADING
"<+0 <COL0<+0 <COL1<+0 <COL2"
PRINT SEATS
BY COUNTRY
BY CAR NOPRINT
ON COUNTRY RECAP
FLD0/A250= COUNTRY | '/ ' | CAR;
CLRTYP/A500='<td align=left  x:num x:fmla=''=A1''>0</td>';
ON COUNTRY SUBFOOT
"<FLD0<CLRTYP"
ON TABLE SUBHEAD
"SUMMARY - BY CLEARANCE - TYPE"
ON TABLE SUBFOOT
""
""
ON TABLE SET STYLE *
TYPE=TITLE, COLOR=NAVY, STYLE=BOLD+UNDERLINE, $
TYPE=REPORT, TITLETEXT='Testing', $
TYPE=TABHEADING,STYLE=BOLD,$
TYPE=SUBHEAD,STYLE=BOLD,$
TYPE=HEADING, LINE=1, STYLE=BOLD+UNDERLINE, COLOR=NAVY,$
TYPE=HEADING,HEADALIGN=BODY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=1, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=2, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
TYPE=HEADING,OBJECT=TEXT,LINE=1,ITEM=3, STYLE=BOLD+UNDERLINE,
COLOR=NAVY,$
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Silver Member
posted Hide Post
Sandhya,
Just to let you know, the Excel .mht files will work in Excel 2003 and earlier. Microsoft Excel 2007 does not create .mht files; however, if you have an earlier version of Excel you can create the template in it and the WebFOCUS templates will still work. If you have WF version 7.7+, I believe you can use the EXL07 format to create templates.


WF 8.1.05 on Windows machines
Backend: Informix, SQL and Oracle databases
 
Posts: 37 | Location: Houston, Texas | Registered: May 01, 2008Report This Post
Gold member
posted Hide Post
Emily,

You are correct; however, when I used the GUI to select "Excel Formula", the only templates it recognized were my .mht files and not any template created for Excel 2007.

Just a fyi ...


WF 7.6.10, Windows, PDF, Excel
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Report This Post
Member
posted Hide Post
There was an Excel presentation "Summit2011Lab_ExcelSolutions.pdf" by Kathy Kendall in the 'Information Builders Summit 2011 User Conference' that may be of use to you. Hope this helps.


WebFocus 7611
Excel/PDF/HTML
 
Posts: 1 | Registered: December 06, 2011Report 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     [CLOSED] Excel Formula with multiple tabs

Copyright © 1996-2020 Information Builders