Previous sheet reference

[NMB311]

I have a spreadsheet that displays the weekly sales of my employees. I have a table that displays the weekly sales and then one below it on the same sheet that displays the year to date sales which are calculated by adding the current week sales to the previous weeks (which are displayed on the previous sheet). Each week I create a new sheet by copying the last sheet and changing the references in the year to date table to refer to the previous sheet's name.

My question is: Is there a way to refer to the previous sheet without having to actually change the references every time?

What I want is something like this: ='cell containing current weeks sales' + 'cell containing year to date sales from the previous sheet' (e.g. =c32+'PreviousSheet'(c48))

 

[sulakvea]

and by changing references you mean you replace references? as in ctrl+h / Find What = Old Name, Replace With = New Name? and then alt+a

 

[Domenic]

Try the following custom function, which needs to be placed in a standard module...

Option Explicit
Public Function PreviousSheet(Optional Cell As Variant)
    Dim WksNum As Long
    Dim wks As Worksheet
    Application.Volatile
    If IsMissing(Cell) Then Set Cell = Application.Caller
    WksNum = 1
    For Each wks In Cell.Parent.Parent.Worksheets
        If wks.Name = Cell.Parent.Name Then
            PreviousSheet = Workbooks(Cell.Parent.Parent.Name).Worksheets(WksNum - 1).Range(Cell(1).Address)
            Exit Function
        Else
            WksNum = WksNum + 1
        End If
    Next wks
End Function

Then the custom function can be used on the worksheet in the following manner...

=PreviousSheet()

=PreviousSheet(B2)

=PreviousSheet([Book2.xlsm]Sheet2!B2)

Note that a reference is optional. So, for example, if the function is entered in B10 and no reference is supplied, the function returns the value from B10 of the previous sheet.

 

[thegurumonkey]

Try the following custom function, which needs to be placed in a standard module...

Option Explicit
Public Function PreviousSheet(Optional Cell As Variant)
    Dim WksNum As Long
    Dim wks As Worksheet
    Application.Volatile
    If IsMissing(Cell) Then Set Cell = Application.Caller
    WksNum = 1
    For Each wks In Cell.Parent.Parent.Worksheets
        If wks.Name = Cell.Parent.Name Then
            PreviousSheet = Workbooks(Cell.Parent.Parent.Name).Worksheets(WksNum - 1).Range(Cell(1).Address)
            Exit Function
        Else
            WksNum = WksNum + 1
        End If
    Next wks
End Function

Then the custom function can be used on the worksheet in the following manner...

=PreviousSheet()

=PreviousSheet(B2)

=PreviousSheet([Book2.xlsm]Sheet2!B2)

Note that a reference is optional. So, for example, if the function is entered in B10 and no reference is supplied, the function returns the value from B10 of the previous sheet.

Hi Domenic (or anyone else)

Really useful code. Having got pretty adept with formulae, partly as a consequence of avoiding VBA, I have now BROKEN... as your UDF above is just too ****ed useful (i hope) and might get me out of a spot of bother with a huge 3D references nightmare. I'm hoping to replace a lot of uses of INDIRECT() with PreviousSheet()

BUT I'm a noob and need a little clarifying on how to tailor the above to my needs.

1) Volatility
I'm doing this change to avoid INDIRECT(), which is unavoidably volatile, and consequently makes my workbook unusable except in 'Manual calc' mode.
So I need to make the UDF above non-volatile, for which I gather that I need to simply remove that line of code, right?
Once that's done, I'm guessing it will recalculate if the reference between the brackets of 'PreviousSheet()' are altered, but not all the time, right?
Will it still re-calculate when I 'calculate now' (F9), or when I first open the sheet?
If the main need for volatility is to stop errors caused when moving sheets, I don't plan to change the order of them ever...

2) Change name
To save time (as i might be using this a lot), I'd like to shorten the name to

Prev()

I assume that'll happen if I just replace both instances of "PreviousSheet()" with "Prev()"
Is this correct?


3) NextSheet()
I'm gonna need the opposite UDF

NextSheet()

any chance someone could alter the above to give me that. I'm guessing I just need to replace the UDF name (as above) and change the sign in either or both of these bits:

NextSheet = Workbooks(Cell.Parent.Parent.Name).Worksheets(WksNum + 1).Range(Cell(1).Address)

...and...

Else
WksNum = WksNum - 1

Or is there more variables to consider?


HUGE thanks people. This could revolutionise my excel use!

 

[oscarh02]

Hi Domenic,

I tried by tiping PreviousSheets() but it does not work on Office2016, I understand the post was a while ago. How can reference a previous sheet now?

Thank you in advance.