Reference "previous sheet"

English
#1

I used to use the prevsheet() function in excel to reference a particular cell from the previous sheet. How can I do this in Librecalc?

Additionally, it needs to stay functional, if a sheet is duplicated and inserted. So it can’t be a one time lookup of the previous sheet’s name and then use that, it must always reference whatever the previous sheet is.

Here is a screenshot of the excel sheet:

1304×866 14.6 KB

The “PTD” row means “paid to date” and is a running total of the totals up until that sheet. So for this particular cell that’s highlighted (F33), it would need to reference cell F33 from the previous sheet (in this case it’s referencing “January” but it could become “January (2)” ). And then that acquired value is added to F32 of the current sheet, and displayed in F33 of the current sheet.

March will reference this value and do the same etc…

How could I achieve this?

#2

Normally, you use relative references to sheets the same way as relative references to cells: omitting the $ before the sheet name. This works in the normal sense that when you copy the formula to another sheet, the result will reference sheets relatively.

But this relative reference has the same property as relative references to rows and columns: if you insert a row between a formula and its reference, formula keeps pointing to the cell it was pointing before, not in the newly inserted row. Likewise, inserting a sheet between this one and the previous, relative sheet references will still point to old sheet, not newly inserted one…

#3

Yes…I’ve discovered this. Is there anyway to keep that “previous sheet” reference an open ended variable? I’m literally just looking for an equivalent function to excel
s “prevsheet()” function. Idk if that works in the 2016 excel and newer, but it worked in excel 2007.

Or some kind of workaround would suffice. I found this: (CALC) Reference to the previous tab in same doc but idk how to get it to work, or how to use macros in Librecalc. Would the macro be running all the time in the background? What would trigger it?

#4

Well - there was never a built-in “prevsheet” in Excel; what you refer to is also a macro custom function - so @JohnSUN’s macro is just what you are looking for.

#5

Now that I dig into it, I see that you’re correct regarding the excel function. Sorry for the confusion. Still not really sure how to make this macro work from JohnSUN, I will try one more time with it, maybe he would be willing to break it down into simpler terms for a newbie…

#6

@JohnSUN and Mike, this is what I have for my macro. https://i.imgur.com/62uYuMZ.png and it works!!!

One last hiccup. The issue is with the first month of the year: https://i.imgur.com/eE8F0sP.png if I enter the formula:

=INDIRECT(SHEETNAME(SHEET()-1)&".C33")+C32

in cell Cell C33 of January for example, it gives an error. I need it to work on every sheet, even the first, in case I need to duplicate the January month sheet to create “January (2)” to continue with entries for the month of January. So if I omit that formula from the January sheet, then the “January (2)” would not carry the total from the first “January” sheet.

Would I add another If statement within the ELSE statement, to state that if the previous sheet index is <1, THEN the cell value becomes =C32 . If this is true, how would it look? I understand conditional statements, but I’m not familiar with the syntax in libre products yet.

#7

Use something like =IFERROR(INDIRECT(SHEETNAME(SHEET()-1)&".C33");0)+C32 to return 0 for missing previous sheet.

#8

Tried inputting that as the formula for cell C33 in January, and got this error: https://i.imgur.com/CMNQ7mW.png

If I add anything to column C and press enter, I get that error, but after hitting OK, cell C33 and C32 are equal the value I put in a cell in Column C (so it’s working). So the question is how to resolve the error I suppose.

#9

Use something like this:

Function SheetName(Optional nSheet As Long)
  On Error Goto errhandler
  If IsMissing(nSheet) Then
    SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
  Else
    SheetName = ThisComponent.getSheets().getByIndex(nSheet-1).getName()
  EndIf
  Exit Function
errhandler:
  SheetName = ""
  On Error Goto 0
End Function
#10

That worked!!! Thank you so much!!!

Where is this macro stored? Is it stored within the LibreCalc document?

#11

According to My Macros & Dialogs.Standard - LibreOffice Basic - Imgur, no. It’s in “Standard” library, independent of any document, and is in user profile.

#12

Interesting. Well I’ll have to find a way to capture that since I may use this on multiple devices. Thanks again for all of your help. Excel is all my mother uses her laptop for, and that is for tax season. So this will allow me to put Linux Mint on her Laptop so I will never have to deal with Windows 10.

You’ve already helped me so much…but if you happen to know how to make a button macro so she can just click it and it will duplicate the current sheet and insert it after the current sheet? If I’m asking too much, please ignore and I’ll understand. Thanks again for all of your help.

Also, I’m new to this forum, how do I mark a comment as the answer?

#13

Tools→Customize...; open Toolbars tab; under Category, select Macros in the drop-down list; find your macro; use arrows to add it to the toolbar selected on the right.

#14

answer is to use this macro:

Function SheetName(Optional nSheet As Long)
  On Error Goto errhandler
  If IsMissing(nSheet) Then
    SheetName = ThisComponent.getCurrentController().getActiveSheet().getName()
  Else
    SheetName = ThisComponent.getSheets().getByIndex(nSheet-1).getName()
  EndIf
  Exit Function
errhandler:
  SheetName = ""
  On Error Goto 0
End Function

and in the cell that you want to reflect a value from the previous sheet, enter the following formula:

 =IFERROR(INDIRECT(SHEETNAME(SHEET()-1)&".C33");0)

where C33 is the cell you wish this cell to reference on the previous sheet.

Thanks again to Mike Kagnanski and JohnSUN

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.