Hi everyone!
I am working on an Inventory sheet for my business and it is not allowing me to do what I need, IE(CircularReference Error).
I have a [Starting Inventory] set at 20 in my 'Master Inventory' Sheet. Then I have 2 Forms that are filling in the [Inventory IN] and [Inventory OUT] as my team re-stocks and takes off items from the shelf respectively in another Sheet named 'Inventory Process'
My final element is an [Update Master Inventory] within 'Inventory Process' Sheet that calculates how many items were put on and adds it to the [Starting Inventory] and visa versa with items taken off the shelf. This then feeds BACK to my 'Master Inventory' Sheet to [Updated Inventory], which is where my #CIRCULAR REFERENCE error occurs since I then want to plug in my [Updated Inventory] into my original formulas as staff takes items off the shelf and re-stocks...
I have this formula: (I am writing the formula out in a way that makes my process easy to understand instead of copy pasting it exactly as it should work. The formula workings isn't my question per say, mainly how I could possibly 'work around' this Circular Reference Error since I have read they can not be bypassed...
=IF(AND([Inventory IN]@row > 0, {Master Inventory [Updated Inventory] 1} > 0), {Master Inventory [Updated Inventory] 1} + [Inventory IN]@row, if not then add to starting inventory {Master Inventory [Starting Inventory] + [Inventory IN])
Then,
[Update Master Inventory] = [Inventory IN], this then links to {Master Inventory [Updated Inventory]} which is where my Circular Reference happens trying to feed it back into the original formula above.
This is just for the Inventory IN process, but the Inventory OUT process links to the same and works just the same except with a - instead of a + etc.
Any thoughts or help is much appreciated!
-Brantley