Page 1 of 1
[Solved] Access sheet by index rather than name
Posted: Sun May 18, 2008 8:24 pm
by 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,
Re: Access sheet by index rather than name
Posted: Sun May 18, 2008 8:56 pm
by 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"
Re: Access sheet by index rather than name
Posted: Sat May 24, 2008 11:21 am
by GyroTech
Thanks a bunch, with your help I got it working pretty much how I wanted
Re: Access sheet by index rather than name
Posted: Sun Jun 08, 2008 9:39 am
by hrlngrv
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
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.
Re: Access sheet by index rather than name
Posted: Sun Jun 08, 2008 10:16 am
by Villeroy
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
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
Re: Access sheet by index rather than name
Posted: Mon Jun 09, 2008 5:24 am
by 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?
Re: [Solved] Access sheet by index rather than name
Posted: Mon Jun 09, 2008 1:23 pm
by 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())