this was asked back in 2014 but I’m hoping an equivalent function to excel’s sheetOffset() now exist.
any hope?
mas
In Calc formulas, sheet references can be absolute ($Sheet1.A1) or relative (Sheet1.A1) just like the row and column references.
1 Like
Asked or did somebody filed an request for enhancement? Can you give the links to your question?.
.
Feature requests should go directly to Bugzilla as Enhancements
is cited from here:
It could be tdf#157062 (assuming OP was referring to Is there a function to access the current sheet minus one.
I’m trying to remove my need for MSoft from my dally life as they are clearly trying to break existing stuff in order to push their newer c__p. 1st comes the software tools then I’ll toss window if I can.
I did a search before I posted this question and just added “(still)” to the only result I found. since I’m still in the evaluation phase, I do not think I’m a good candidate to be asking for enhancements or reporting bugs nor do I know if someone has already has do so.
Such a function may and will cause a lot of volatility in the whole workbook because it will be affected by insertion and removal of sheets.
it’s a function I’ve been using for the last 8 + years. it works fine in excel when I make a copy of the current sheet to be use for the upcoming week. I need stuff from the last week to automatically be rolled into the new copy. it also lets corrections done on weeks gone by to automatically be forwarded to the current week. otherwise you are correct, removal of earlier sheets/weeks is a No No.
Python code:
doc = XSCRIPTCONTEXT.getDocument()
sheet = doc.CurrentController.ActiveSheet
index = sheet.RangeAddress.Sheet
sheet = doc.Sheets[index-1]
2 Likes
is python built into Calk like basic is built into excel? otherwise I can not install it on my main system as it already has an old “frozen” version (2.8)
UDF in Calc can’t directly return references. An absolute name for a referenced SheetCellRange, however, can be turned into a reference using INDIRECT(). There is the attached example (raw sketch).
To avoid a too large number of needed arguments, it uses VBAsupport 1 in Basic.
disask110117rawExampleWithSheetOffset_kindof.ods (15.8 KB)
I have a sample serial sheets which may be matched your need.
SampleReferSheetNameByIndirect.ods (19.7 KB)
It is just a workaround that I used in my works. I took sheet names that have component of Date like Y-M-W-D or what so ever then manipulate them in cell. After that I used INDIRECT function to make previous dated data available on current sheet. Sorry for my bad english. Let me know if this work out for your case.
Thanks Lupp and TingliChan for your examples. I’ll use them and see if I can get my “auto week add” to work. I’ve used indirect() in excel before so at least I understand what your goals are.
[BTW
@masman2k: This is nout about our goals but about yours.
The “sheet-per-week-approach” is doubtable anyway.
/]
The attached new example contains a workaround for this issue.
See the array formula for CurrentWorkSheet.C21:D23 there.
disask110117rawExampleWithSheetOffset_kindof_enhanced.ods (24.3 KB)
sorry for the delay…
the sheet-per-week approach has worked for the last 10+ years and creates a history I we need during the quarterly review. switch to LO can’t change our admin process or it will not be allowed to happen.
I try you latest file and only get “values” for the cells. thanks for trying, perhaps you may wish to fix it for any one looking at this page. I was able to use the previous files to get a working version. have not switch to LO yet do to some other issues in Writer.
Thank you all who responded.