| View previous topic :: View next topic |
| Author |
Message |
Albireo
General User
Joined: 11 Feb 2009
Posts: 35
Location: Sweden
|
 Posted: Mon Jun 08, 2009 7:45 am Post subject: Name of the spreadsheet |
 |
|
Can You see what the spreadsheet is called?
I intend to do a chart which summarizes the values from different worksheets.
for ex.
Have named the different spreadsheets W21, W22, W23, W24
The diagram on worksheets W21 gets its values from W21, W22, W23
and
The diagram on worksheets W22 gets its values from W22, W23, W24
and so on
Can I just know what the spreadsheet called,
maybe I can manipulate the string so I get a reference such as "= W22.L1"
and so on....
//Jan |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
Albireo
General User
Joined: 11 Feb 2009
Posts: 35
Location: Sweden
|
| Posted: Mon Jun 08, 2009 10:29 am Post subject: |
|
|
Pleasant view of the trigonometric formulas.
Sorry, but it wasn't what I seek.
Maybe I use wrong word!
(I mean not the name on the cell)
The tab has the name W21, W22, W23
(the name You can see at the bottom on the spreedsheet)
//Jan |
|
| Back to top |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
| Posted: Mon Jun 08, 2009 11:31 am Post subject: |
|
|
I'm not sure ... you would like a formula to get the table name?
Use: | Code: | | RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("#";CELL("filename";A1))-1) |
HTH
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Jun 08, 2009 11:38 am Post subject: |
|
|
Each of the scenarios Sinus, Cosinus, Tangens,... refers to an equally named hidden sheet-tab. Scenarios are just a different way to present raw data from different sheets on the same formatted sheet.
Function FORMULA can show the formula of another cell, so you see the referenced addresses, such as SheetX.A1.
CELL("FILENAME") returns the full URL-path of the document plus name of current sheet: 'file:///path/name.ods'#SheetX
CELL("FILENAME"; SheetY.A1) returns the same for SheetY since there is an additional reference to that sheet.
probe1's above formula extracts the sheet-part from that string.
A most simple Basic function can be used to show all sheet names:
| Code: | Function SHEETNAMES()
SHEETNAMES = ThisComponent.Sheets.getElementNames()
End Function |
Array function =SHEETNAMES() [Ctrl+Shift+Enter] returns a horizontal list of names.
Array function =TRANSPOSE(SHEETNAMES()) [Ctrl+Shift+Enter] returns a vertical list of names.
TABLE(SheetX.A1) returns the position of SheetX.
=INDEX(SHEETNAMES();0;TABLE(SheetX.A1)) returns the name of the referred sheet, even after it had been renamed and/or moved.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Albireo
General User
Joined: 11 Feb 2009
Posts: 35
Location: Sweden
|
| Posted: Mon Jun 08, 2009 6:16 pm Post subject: |
|
|
| Villeroy wrote: |
Each of the scenarios Sinus, Cosinus, Tangens,... refers to an equally named hidden sheet-tab. Scenarios are just a different way to present raw data from different sheets on the same formatted sheet.
. |
I saw at the bottom left corner, that there were 10 hidden forms (I can't "se" them or use them)
| Villeroy wrote: |
CELL("FILENAME") returns the full URL-path of the document plus name of current sheet: 'file:///path/name.ods'#SheetX
CELL("FILENAME"; SheetY.A1) returns the same for SheetY since there is an additional reference to that sheet.
probe1's above formula extracts the sheet-part from that string.
. |
Yes this works for me!
RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("#";CELL("filename";A1))-1)
But
if I change the name of the tab, change does not result in cell
Why?
(Now I see that the cell is updated when
the spreadsheet is closed and opened again ...)
| Villeroy wrote: |
A most simple Basic function can be used to show all sheet names:
| Code: | Function SHEETNAMES()
SHEETNAMES = ThisComponent.Sheets.getElementNames()
End Function |
Array function =SHEETNAMES() [Ctrl+Shift+Enter] returns a horizontal list of names.
Array function =TRANSPOSE(SHEETNAMES()) [Ctrl+Shift+Enter] returns a vertical list of names.
TABLE(SheetX.A1) returns the position of SheetX.
=INDEX(SHEETNAMES();0;TABLE(SheetX.A1)) returns the name of the referred sheet, even after it had been renamed and/or moved
. |
How can I use this?
(the small basic program? where will the code be placed? )
//Jan |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Jun 08, 2009 11:41 pm Post subject: |
|
|
| Quote: | | if I change the name of the tab, change does not result in cell |
Hit Ctrl+Shift+F9 to enforce a completer recalculation after
Since none of the input parameters of the CELL function did change, there is no need to recalculate the function. The string "FILENAME" always remains the same. You may trigger automatic recalculation when you change the value of the cell used as second argument in CELL("FILENAME"; SheetY.A1) .
An dirty hack is: CELL("FILENAME"; SheetY.A1) &T(RAND())
The random number is calculated on any change in the spreadsheet.
| Quote: | How can I use this?
(the small basic program? where will the code be placed? ) |
[Tutorial] How to install a code snippet
Userdefined Basic function need to be in a "Standard" library in order to be loaded on start and being found by the spreadsheet component.
Same issue with recalculation trigger. Enforce with SHEETNAMES(RAND()). The additional argument triggers, but is simply not used by the code.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Albireo
General User
Joined: 11 Feb 2009
Posts: 35
Location: Sweden
|
| Posted: Tue Jun 09, 2009 5:53 am Post subject: |
|
|
| Thank You! |
|
| Back to top |
|
|
|
