Reference cell in previous sheet

Copper Contributor

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 

 

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.

@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