Avoiding circular references

JBG ✭✭✭
edited 11/01/23 in Formulas and Functions

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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!