Relative referral to cell on previous sheet

[andrewvanmarle]

Hi all,

I googled this first, found several sites citing the same VBA code:

Function PrevSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    PrevSheet = Sheets(i - 1).Range(rCell.Address)
End Function

And then enter this to the cell in question:

=PrevSheet(A1)


but I get no value....

I assume im doing something wrong??

 

[Joe4]

Have you enabled VBA in your workbook?
Are you trying to use this on some sheet other than the first sheet?

You should get something return (i.e. at least a 0 or a #VALUE error).

 

[andrewvanmarle]

Hi Joe,

Yeah VBA is enabled. And i'd use this on every sheet, referring to the previous one. (everysheet is a year and I want to transport the end value to the next....)

 

[Joe4]

And i'd use this on every sheet,

You cannot use it on the first sheet, as there is no previous sheet to the first sheet.

Where exactly have you placed this VBA code?
To make it accessible from ALL pages, you need to put it in a new, general module.
If you put it in one of the pre-defined sheet modules, it will only work on that sheet.

 

[andrewvanmarle]

Ohhhhhhhh


Oooooops

It should be next sheet.....
I feel like an idiot...

What do I modify?

Sheets(i + 1)

?

 

[Joe4]

If its next sheet, I would create a new similar UDF, i.e.

Function NextSheet(rCell As Range)
    Application.Volatile
    Dim i As Integer
    i = rCell.Cells(1).Parent.Index
    NextSheet = Sheets(i + 1).Range(rCell.Address)
End Function

 

[andrewvanmarle]

I'm trying it right away, thank you!!

 

[Joe4]

You are welcome.

 

[andrewvanmarle]

By the way,

If I want to do this without VBA, would this idea work?

Sheet names are years, and A1 is manually filled in with the year of the sheet (ex 2020, 2021 etc)

Can I refer to a cell on a sheet by using the value on A1 minus one? essentially this:

='2020'!F2

Where 2020 would be a1-1.

I tried that but that made excel try to open a file (weird)

 

[Joe4]

You can use the INDIRECT function to do that.
Basically, you build the range reference formulaically, then surround it in INDIRECT, i.e.

=INDIRECT("'" & A1-1 & "'!F2")