Reference cell in previous sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1502920%22%20slang%3D%22en-US%22%3EReference%20cell%20in%20previous%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1502920%22%20slang%3D%22en-US%22%3E%3CP%3EHow%20do%20I%20reference%20the%20value%20of%20a%20cell%20in%20a%20previous%20sheet%20without%20having%20to%20name%20it%3F%20I%20have%20tried%20the%20SHEETOFFSET%20formula%2C%20and%20it%20doesn't%20work%20on%20my%20.xlsm%20workbook.%3CBR%20%2F%3EI%20am%20trying%20to%20pull%20in%20number%20of%20hours%20from%20previous%20timesheet%20in%20same%20cell%2C%20previous%20worksheet.%20From%20what%20I%20saw%2C%20%3DSHEETOFFSET(-1%2C%20J23)%20should%20return%20the%20value%20from%20the%20cell%20J23%20in%20the%20previous%20sheet%20without%20having%20to%20use%20%3D'previoussheet'!%2CJ23.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1502920%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional 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