Forum Discussion
Reference cell in previous sheet
How do I reference the value of a cell in a previous sheet without having to name it? I have tried the SHEETOFFSET formula, and it doesn't work on my .xlsm workbook.
I am trying to pull in number of hours from previous timesheet in same cell, previous worksheet. From what I saw, =SHEETOFFSET(-1, J23) should return the value from the cell J23 in the previous sheet without having to use ='previoussheet'!,J23.
Thanks!
4 Replies
cre8v1 thx I learned something new 🙂 This problem sounds like another I answered recently and discovered my VBA wasn't 'volatile' and wasn't auto-calculating so I fixed it based on the published code for SHEETOFFSET.
That said I hope that you realize that function is not built into Excel and you have to go into VBA and copy that code into a module there. It is easy if you need help. Otherwise the code seems to work correctly for me.
I recommend you look at my previous post as in addition to the VBA solution I also suggested a LOOKUP solution where you can add a sheet with a list of the Sheet Names you have and then use an INDIRECT function (see other post).
I also just did it using the Name Manager adding 2 custom Names:
sNames = {"Sheet1","Sheet2","Sheet3"} <= this lists all the sheet names
pSheet = INDIRECT("'"&INDEX(sNames,SHEET()-1)&"'!R"&ROW()&"C"&COLUMN(),FALSE)
then where ever you type =pSheet() it will give you the value from the previous sheet
So I just created a new NAME Formula using an excel 4 macro function that appears to work in getting "previous sheet value". So if you want to 'carry over' the value in sheet1!A1 to sheet2!A1 OR maybe create a cumulative total from prior sheets (i.e. previousSheet + value on this sheet). Here is the definition for the name (entered in Name Manager):
prevSheet = INDIRECT(INDEX(GET.WORKBOOK(1),SHEET()-1)&"'!R"&ROW()&"C"&COLUMN(),FALSE)
then using the name prevSheet will return the value for the previous sheet
What is the issue with ='previoussheet'!,J23 method - If it's not working try ='previoussheet'!J23
Alternatively, type '=' in the cell where you want the linked value, then click on the previoussheet tab and select the cell with the required value and hit enter.
I hope this is helpful.
Thank you (five years later). The Alternative worked for me.
However, it would have been nice if previoussheet worked too but it seemed to interpret previoussheet as a file name.
