[Solved] Access sheet by index rather than name

[GyroTech]

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,

[Villeroy]

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

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"

[GyroTech]

Thanks a bunch, with your help I got it working pretty much how I wanted

[hrlngrv]

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

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.

[Villeroy]

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

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

[hrlngrv]

Actually I'd use something more like

Code: Select all

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?

[Villeroy]

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

=IF(RAND();INDEX(SHEETLIST();0;ROW())