How do I select a prior list item without getting a circular reference
I am trying to build a model that looks like the above screen shot. Tier 1 - Tier 5 are list items and everything in the top row are list items. I am having trouble getting any ending balance to work (for reference, I'm able to begin the tier 2 starting balance and so on and so forth to pull the ending balance from the prior tier) but I can't put a simple subtraction formula in ending balance without getting a circular reference. I've tried adding a mapping column called previous tier that is formatted by the tier list and doing a lookup but that is giving me the same issue.
Answers
-
I tried to get the values that are in your Excel. We can make Map Tier line item as formula driven if the pattern of fetching values is constant between Starting Balance and Ending Balance.
0 -
My starting balances aren't be input driven, the tier 1 starting balance is coming from a different module, the ending balance would also be formulaic (starting balance - tier 1 amount) and then starting balance for each additional tier would be the ending balance from the prior tier.
0 -
I've tried it with the mapping tiers this way as well and I still can't avoid a circular reference
0 -
Are you referring ending balance in starting balance formula directly or indirectly?
0 -
directly. if my tier 1 ending balance is $20,000 then I need that to be my tier 2 starting balance. My starting balance formula is if item(tiers) = tier 1 then spend (from another module) else prior tier ending balance. My ending balance formula is starting balance minus amount for calc
0 -
Is it possible to share formulas or screenshots of modules for better understanding?
0 -
Hi @wld002
Very Interesting and realistic Question
So, As per the above discussion I have to understood that opening balance = previous (Closing Balance) and Closing balance = Opening balance - Additional calc
As Formulas itself is telling the circular reference
To my knowledge there no direct way to achieve this
So, I have created an intermediate line item which stores opening balance by running an import and I have referred that intermediate line item in Closing balance.
This is one workaround through which we can achieve desired result.
Let me know if this helps or if you find out closing balance independent of opening balance then you will not get circular reference.0
