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