Forum Discussion
Formula not updating on new sheet
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
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.
