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.

Accepted answers

All comments

Dikshant

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.

wld002

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.

wld002

I've tried it with the mapping tiers this way as well and I still can't avoid a circular reference

Dikshant

Are you referring ending balance in starting balance formula directly or indirectly?

wld002

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

Dikshant

Is it possible to share formulas or screenshots of modules for better understanding?

PujithaB

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.

Quick Links

Can you help?

Unanswered questions

Azure blob connection Cloudworks vs ADO
I am trying to connect to Azure blob, the credentials work on cloudworks, but do not work on ADO. Has anyone else experienced this issue?
Berapa nomor Tokopedia?
Tokopedia Menyediakan layanan WhatsApp? Untuk menghubungi layanan Tokopedia melalui (WA)62_08174_9080_16 kamu bisa mengikuti.
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…