OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Name of the spreadsheet

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Albireo
General User
General User


Joined: 11 Feb 2009
Posts: 35
Location: Sweden

PostPosted: Mon Jun 08, 2009 7:45 am    Post subject: Name of the spreadsheet Reply with quote

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
View user's profile Send private message Send e-mail
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jun 08, 2009 9:21 am    Post subject: Reply with quote

http://user.services.openoffice.org/en/forum/download/file.php?id=3004
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Albireo
General User
General User


Joined: 11 Feb 2009
Posts: 35
Location: Sweden

PostPosted: Mon Jun 08, 2009 10:29 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
probe1
Moderator
Moderator


Joined: 18 Aug 2004
Posts: 2560
Location: Chonburi Thailand Asia

PostPosted: Mon Jun 08, 2009 11:31 am    Post subject: Reply with quote

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
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jun 08, 2009 11:38 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Albireo
General User
General User


Joined: 11 Feb 2009
Posts: 35
Location: Sweden

PostPosted: Mon Jun 08, 2009 6:16 pm    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jun 08, 2009 11:41 pm    Post subject: Reply with quote

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
Quote:
Why?

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Albireo
General User
General User


Joined: 11 Feb 2009
Posts: 35
Location: Sweden

PostPosted: Tue Jun 09, 2009 5:53 am    Post subject: Reply with quote

Thank You!
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group