Forum Discussion
Formula not updating on new sheet
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....
mtarler Thanks for reply.Followed your steps but got #SPILL! error and dont have solution to it.
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.