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

reference a spreadsheet by number, not by name

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


Joined: 12 Jun 2012
Posts: 1

PostPosted: Tue Jun 12, 2012 8:17 am    Post subject: reference a spreadsheet by number, not by name Reply with quote

I find the functions SHEET() and SHEETS() that respectively return a sheet number from a sheet name and the total count of sheets in a document.

I need to use the sheet number that I obtained from the SHEET() function to indirectly address a cell from a different sheet, i.e. I want to have a sheet that picks information from a specific cell in each sheet of the document and I want to do this by using native functions, not macros.

I already managed to get the sheet name for the sheet where a cell is located and I have the first cell in each sheet containing the name of the sheet, but this is of no avail because I cannot address the sheets other than by name, so far.

I suppose there must be a function, or some tricky mode of it, or some formatting convention of references that may allow it, but I cannot find it.

------

Well I could not wait and found the solution:

In each sheet I put a hidden cell that is set "=" to the cell containing the sheet name in the following sheet. So in each sheet I have a cell (A1) containing:
=HYPERLINK("#Index.A"&SHEET()-1,MID(CELL("filename"),FIND("'#$",CELL("filename"),1)+3,100))
and a cell (F1) containing:
='{next sheet}'.A1 ({next sheet} is just the next one from each sheet where I insert the hidden F1 cell. I select the range of sheets and copy the F1 cell, calc automatically fills with the name of the next sheet

Then I put as first sheet my index sheet that contains as many lines as sheets in the document minus one. The first row is:
=HYPERLINK("#"&'{my first sheet}'.A1&".A1",'{my first sheet}'.A1)
and all the following ones are:
=HYPERLINK("#"&INDIRECT("'"&A1&"'!F1")&".A1",INDIRECT("'"&A1&"'!F1"))

With this I have the index where I can click on each sheet name to jump into the sheet and then from the sheet click on the name and jump back to the index.



Help is very appreciated
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Jun 12, 2012 11:28 am    Post subject: Reply with quote

http://www.oooforum.org/forum/viewtopic.phtml?t=111239

and then
=INDEX(SHEETLIST() ; 1 ; Number)
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
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