Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Hyperlink in Excel from one tab to the next
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Hyperlink in Excel from one tab to the next
 Login/Join
 
Platinum Member
posted
I have a compound EXL2K report.
It has two tabs first tab is summary and second tab is detail. Is it possible to have a link on Field1 in Summary Tab which when clicked goes to its position on detail tab?. I tried searching for this in the forum but didn't find any.A car example would be great!.
Thanks.

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


App Studio Version 8202
windows Platform
SQL Server 2008/2012
 
Posts: 179 | Location: TX | Registered: January 22, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
Here is something that I knocked up.

Seems that Excel uses an anchor to link to cells in the workbook.

SET COMPOUND = OPEN

TABLE FILE CAR
PRINT 
COMPUTE CNTR/I3 = IF LAST CNTR EQ 0 THEN 1 ELSE LAST CNTR + 1 ;
COMPUTE CTYLINK/A50 = '<a href="#' || COUNTRY || '!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
END

SET COMPOUND = BYTOC CLOSE

TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Bravo!

-- One shortcoming -- the embedded blank in W GERMANY makes its link invalid.

The fix:
DEFINE FILE CAR
  TAB/A10=STRREP(10, COUNTRY, 1, ' X', 1, '_', 10, TAB);
  TAB=SUBSTR(10, TAB, 1, ARGLEN(10,COUNTRY,'I2'), 10, TAB);
END

SET COMPOUND = OPEN

TABLE FILE CAR
WRITE 
  COMPUTE LIST/I3 = LIST + 1 ;
  COMPUTE CTYLINK/A90 = '<a href="#' || TAB || '!A1">' || COUNTRY || '</a>' ; AS COUNTRY
BY COUNTRY NOPRINT
ON TABLE PCHOLD FORMAT EXL2K 
END

SET COMPOUND =  CLOSE

TABLE FILE CAR
PRINT CAR MODEL
BY TAB  NOPRINT
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K BYTOC
END
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Well spotted.

Seems that Excel will wrap the text in single quotes, so the below will also work.

SET COMPOUND = OPEN

TABLE FILE CAR
PRINT 
COMPUTE CNTR/I3 = IF LAST CNTR EQ 0 THEN 1 ELSE LAST CNTR + 1 ;
COMPUTE CTYLINK/A50 = '<a href="#''' || COUNTRY || '''!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
END

SET COMPOUND = BYTOC CLOSE

TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
ON TABLE PCHOLD FORMAT EXL2K
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
And back!
  
SET COMPOUND = OPEN

TABLE FILE CAR
PRINT 
COMPUTE CTYLINK/A50 = '<a href="#''' || COUNTRY || '''!A1">' || COUNTRY || '</a>' ;
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
 TITLETEXT='Countries', $
ENDSTYLE
END

SET COMPOUND = BYTOC CLOSE
DEFINE FILE CAR
PG1/A50='<a href="#''Countries''!A1">' || 'Countries' || '</a>' ;
END

TABLE FILE CAR
PRINT CAR MODEL
BY COUNTRY
FOOTING
"<PG1 "
ON TABLE PCHOLD FORMAT EXL2K
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1936 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
When will it end.......


Music


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You guys are awesome!
Still trying to get the link to go the exact cell value instead of A1. If we click on France on SUMMARYRPT it goes to the cell where the value of the country is 'FRANCE' IN DETAILRPT.


-*************************************************
DEFINE FILE CAR
CTYLINK/A100 = '<a href="#''' || 'DETAILRPT' || '''!A1">' || COUNTRY || '</a>' ;
END

 TABLE FILE CAR
HEADING
"Summary Report"
" "
SUM RC
BY CTYLINK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='SUMMARYRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Detail Report"
" "
PRINT SALES
BY COUNTRY
BY MODEL
BY CAR
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='DETAILRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END



App Studio Version 8202
windows Platform
SQL Server 2008/2012
 
Posts: 179 | Location: TX | Registered: January 22, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
That's just a matter of replacing the A1 cell reference in the link variable with a computed variable pointing to the appropriate cell -- column A (constant), row = first row for the respective country = cumulative total of rows for previous countries + the offset of the very first detail row.

TABLE FILE CAR
WRITE CNT.SALES
COMPUTE
  OFFSET/I2=LAST OFFSET+LAST CNT.SALES;
  ROW/I2=OFFSET+4;
  CTYLINK/A50 = '<a href="#''DETAILRPT''!A' || EDIT(ROW) || '">' || COUNTRY || '</a>' ;
BY COUNTRY
ON TABLE HOLD AS LINKS FORMAT FOCUS INDEX COUNTRY
END

JOIN CLEAR *
JOIN COUNTRY IN CAR TO COUNTRY IN LINKS AS L:

TABLE FILE CAR
HEADING
"Summary Report"
" "
SUM RC
BY CTYLINK
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='SUMMARYRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K OPEN
END

TABLE FILE CAR
HEADING
"Detail Report"
" "
PRINT SALES
BY COUNTRY
BY MODEL
BY CAR
ON TABLE SET STYLE *
TYPE=REPORT, TITLETEXT='DETAILRPT', $
TYPE=HEADING, SIZE=18, $
ENDSTYLE
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Great , Thanks.
Just one last quesion,can I get a CAR EXAMPLE to win the mega millions?
Just kiddin :-), Thanks a lot.


App Studio Version 8202
windows Platform
SQL Server 2008/2012
 
Posts: 179 | Location: TX | Registered: January 22, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Hyperlink in Excel from one tab to the next

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.