Hi all, i am curious if someone might have a solution to this challenge. Programmatically it is not complicated, but using tools like smartsheet or excel with formulas, etc. you run into the circular reference challenge.
I have a sheet which contains 6 columns, "empemail", "calendaryear", "allocation", "broughtfwd", "used", "balance"
"Empemail" and "calendaryear" is populated with automation and other ways. "allocation" is a formula calculating the hours allocated, "broughtfwd" is the value of "balance" for the previous "calyear", "used" is a sumif function to a different sheet and "balance" is ("allocation"+"broughtfwd")-"used".
An example is:
2022, johndoe@joe.com, 100, 20, 80, 40. When using a formula to have the "balance" of 40 for 2022 as "broughtfwd" for 2023 you get the circular reference issue.
I have been trying and thinking of moving information to other sheets, but you always have this circular reference.
Any help would be highly appreciated.
