Forum Discussion
Reference cell in previous sheet
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