Avoiding circular references

JBG
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.

================================================

"Nothing is impossible. The word itself says 'I'm possible!'"

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!