Ask Your Question
0

Reference "previous sheet"

asked 2019-03-19 18:02:23 +0200

Boggle gravatar image

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:

https://i.imgur.com/IiqXLlv.png

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?

edit retag flag offensive close merge delete

Comments

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...

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-19 18:16:31 +0200 )edit

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: https://ask.libreoffice.org/en/questi... 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?

Boggle gravatar imageBoggle ( 2019-03-19 18:30:59 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-19 18:42:31 +0200 )edit

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.....

Boggle gravatar imageBoggle ( 2019-03-19 18:44:54 +0200 )edit

@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 ...(more)

Boggle gravatar imageBoggle ( 2019-03-19 19:56:19 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-19 20:00:54 +0200 )edit

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.

Boggle gravatar imageBoggle ( 2019-03-19 20:17:52 +0200 )edit

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
Mike Kaganski gravatar imageMike Kaganski ( 2019-03-19 21:07:32 +0200 )edit

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

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

Boggle gravatar imageBoggle ( 2019-03-20 00:30:59 +0200 )edit

According to https://imgur.com/62uYuMZ, no. It's in "Standard" library, independent of any document, and is in user profile.

Mike Kaganski gravatar imageMike Kaganski ( 2019-03-20 00:49:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-03-20 01:12:35 +0200

Boggle gravatar image

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-03-19 18:02:23 +0200

Seen: 196 times

Last updated: Mar 20