[Solved] Access sheet by index rather than name

Discuss the spreadsheet application

[Solved] Access sheet by index rather than name

Postby GyroTech » Sun May 18, 2008 8:24 pm

Hi, I was wondering if it is possible to, in a formula, pull out data from the previous sheet, without having to know the sheet name itself.

I found the SHEET() function to get the current sheet's index value, but you can't use that in place of the text name.

Is this possible without delving into macro-land at all??

Thanks,
Last edited by Hagar Delest on Mon Jun 09, 2008 5:29 pm, edited 2 times in total.
Reason: tagged the thread as Solved.
GyroTech
 
Posts: 2
Joined: Sun May 18, 2008 5:15 pm

Re: Access sheet by index rather than name

Postby Villeroy » Sun May 18, 2008 8:56 pm

Is this possible without delving into macro-land at all??

AFAIR it is impossible. It may help to know that Calc knows the concept of absolute and relative sheet references analog to row and column references.
Having the "normal" sequence of Sheet1, Sheet2,...
=Sheet2.$A$1 in some cell on Sheet1 refers to the next sheet. When you copy the formula to Sheet2 it reads =Sheet3.$A$1.
The most minimalistic macro solution is a userdefined cell function in Basic:
Code: Select all   Expand viewCollapse view
Function SHEETLIST()
SHEETLIST = ThisComponent.Sheets.getElementNames()
End Function

Now array-function
=SHEETLIST() [Ctrl+Shift+Enter] returns a horizontal vector of sheet names.
=TRANSPOSE(SHEETLIST()) [Ctrl+Shift+Enter] returns a vertical vector of sheet names.

A1: =INDEX(SHEETLIST();0;3) returns the name of the third sheet.
A2: =ADDRESS(1;1;1;A1) returns "$Sheet3.$A$1"
A3: =INDIRECT(A2) returns a reference to "$Sheet3.$A$1"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28553
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Access sheet by index rather than name

Postby GyroTech » Sat May 24, 2008 11:21 am

Thanks a bunch, with your help I got it working pretty much how I wanted :)
GyroTech
 
Posts: 2
Joined: Sun May 18, 2008 5:15 pm

Re: Access sheet by index rather than name

Postby hrlngrv » Sun Jun 08, 2008 9:39 am

Villeroy wrote:
Is this possible without delving into macro-land at all??

AFAIR it is impossible.
...
The most minimalistic macro solution is a userdefined cell function in Basic:
...

The most minimalistic solution is a direct solution. Also a user-defined function in Basic,

Code: Select all   Expand viewCollapse view
function foo(s as long, r as long, c as long) as variant
   foo = ThisComponent.Sheets.getByIndex(s - 1).getCellByPosition(c - 1, r - 1).Value
end function


Once this code is added, the cell formula =foo(2,5,10) returns the value of cell J5 in the 2nd sheet.
OOo 2.4.X on Ms Windows XP + Ubuntu Linux
hrlngrv
 
Posts: 4
Joined: Sun Jun 08, 2008 7:41 am

Re: Access sheet by index rather than name

Postby Villeroy » Sun Jun 08, 2008 10:16 am

hrlngrv wrote:The most minimalistic solution is a direct solution. Also a user-defined function in Basic

Your function returns the numeric value only while lacking all error checking.
What you actually mean is this one:
Code: Select all   Expand viewCollapse view
Function CELL_VALUE(vSheet,lRowIndex&,iColIndex%)
Dim v, val
   v = getSheetCell(vSheet,lRowIndex&,iColIndex%)
   if vartype(v) = 9 then
      val = getCellValue(v)
      if vartype(val) = 0 then'Empty
         CELL_VALUE = ""
      else
         CELL_VALUE = val
      endif
   else
      CELL_VALUE = v
   endif
End Function
'Helper for sheet functions. Get cell from sheet's name or position; cell's row-position; cell's col-position
Function getSheet(byVal vSheet)
on error goto exitErr
   select case varType(vSheet)
   case is = 8
      if thisComponent.sheets.hasbyName(vSheet) then
         getSheet = thisComponent.sheets.getByName(vSheet)
      else
         getSheet = NULL
      endif
   case 2 to 5
      vSheet = cInt(vSheet)
      'Wow! Calc has sheets with no name at index < 0,
      ' so NOT isNull(oSheet), if vSheet <= lbound(sheets) = CRASH!
      'http://www.openoffice.org/issues/show_bug.cgi?id=58796
      if(vSheet <= thisComponent.getSheets.getCount)AND(vSheet > 0) then
         getSheet = thisComponent.sheets.getByIndex(vSheet -1)
      else
         getSheet = NULL
      endif
   end select
exit function
exitErr:
getSheet = NULL
End Function
Function getSheetCell(byVal vSheet,byVal lRowIndex&,byVal iColIndex%)
dim oSheet
'   print vartype(vsheet)
   oSheet = getSheet(vSheet)
   if varType(oSheet) <>9 then
      getSheetCell = NULL
   elseif (lRowIndex > oSheet.rows.count)OR(lRowIndex < 1) then
      getSheetCell = NULL
   elseif (iColIndex > oSheet.columns.count)OR(iColIndex < 1) then
      getSheetCell = NULL
   else
      getSheetCell = oSheet.getCellByPosition(iColIndex -1,lRowIndex -1)
   endif
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28553
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Access sheet by index rather than name

Postby hrlngrv » Mon Jun 09, 2008 5:24 am

Actually I'd use something more like

Code: Select all   Expand viewCollapse view
function foo(s as long, r as long, c as long) as variant
   Dim tmp as object

   if s > 0 and s <= ThisComponent.Sheets.Count then
      set tmp = ThisComponent.Sheets.getByIndex(s - 1)

      if r > 0 and r <= tmp.Rows.Count and c > 0 and c <= tmp.Columns.Count then
         set tmp = tmp.getCellByPosition(c - 1, r - 1)

         if tmp.Error <> 0 then
              foo = cverr(tmp.String)  'nothing I've tried returns error values
         elseif tmp.Type = 1 or (tmp.Type = 3 and tmp.FormulaResultType = 1) then
             foo = tmp.getValue()
         elseif tmp.Type = 2 or (tmp.Type = 3 and tmp.FormulaResultType = 2) then
             foo = tmp.getString()
         end if

         exit function

      end if

   end if

   foo = cverr("#REF!")
end function


I hadn't realized the there seems to be no way to return values from StarBasic that Calc would treat as error values. What's the point of including the CVErr function in StarBasic?
OOo 2.4.X on Ms Windows XP + Ubuntu Linux
hrlngrv
 
Posts: 4
Joined: Sun Jun 08, 2008 7:41 am

Re: [Solved] Access sheet by index rather than name

Postby Villeroy » Mon Jun 09, 2008 1:23 pm

CVErr? never heard of that one! Well, the original problem could be boiled down to "How to get a list of sheet names" for whatever purpose (others use it for a TOC). My one-liner does this pretty well without the need to handle any errors and it auto-updates by using the same trick we discussed in another thread on function CELL.
Code: Select all   Expand viewCollapse view
=IF(RAND();INDEX(SHEETLIST();0;ROW())
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28553
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 10 guests