Formula not updating on new sheet

[Ralphwork]

Hi

I have formula =J4-'22.07.2020'!J4 on sheet 30.07.2020 but when i COPY 30.07.2020 and rename to 06.08.2020 then the formula remains =J4-'22.07.2020'!J4.

Instead it should be changing to =J4-'30.07.2020'!J4 to continue the formula into the new week and ignoring 22.07.2020.

How to enable this feature because my other openoffice spreadsheet does this on its own but i'd like to continue working solely on excel ?

Regards

[#2]

@Ralphwork  As you noted, excel doesn't do that.  But from you say it sounds like you basically want to add the amount from the previous sheet.  The following Name Definition that should help you with that.  So under Formulas go to Define Name and give a name you want like prevJ4, keep the scope as Workbook (which is default) and for "Refers to:" paste this:

=INDIRECT("'"&INDEX(GET.WORKBOOK(1),SHEET()-1)&"'!J4")

Then in your cell use = J4 - prevJ4 

[#3]

@mtarler  Thanks for reply.Followed your steps but got #SPILL! error and dont have solution to it.

[#4]

@Ralphwork  I am only seeing a "spill" happen on the 1st sheet because there is no previous sheet and therefore you can't use it on the 1st sheet.  If you want you could change the Named Function to:

=IF(SHEET()=1,0,INDIRECT("'"&INDEX(GET.WORKBOOK(1),SHEET()-1)&"'!J4"))

which will force a 0 if you try to use it on the 1st sheet in the workbook.

REMEMBER: this is not getting some arbitrary sheet, it is specifically getting the value of J4 from the PREVIOUS sheet in the workbook.