How to work around this Cirular Reference Error?

Brantley Brumley · 04/13/20

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

Brantley Brumley · 04/13/20

I figured out a work around and will share in case others can have a use for it:

Since I am simply adding and subtracting items, and Smartsheet will not allow a Circular Reference or a dynamic 'Get a solution, plug that solution into the same formula it came from, rinse and repeat' method this is what I did:

In the [Update Master Inventory] column I added the following formula in a cell by itself a little from the top, since this is a Linked cell to the {Master Inventory} Sheet

=SUM([Update Master]:[Update Master]) This will essentially SUM up all the changes in the entire column

I then turned the [Inventory OUT] into a negative number by this formula:

=IF([Inventory OUT]@row > 0 This just checks that there is indeed a value here, 0 - [Inventory OUT]@row)

The [Inventory IN] can stay positive so no changes needed there.

The [Update Master] column then takes the [Inventory OUT] and [Inventory IN] data with this formula:

=IF([Update IN]@row > 0, [Update IN]@row, IF([Update OUT]@row < 0, [Update OUT]@row)) ***Note I have the forms separated so there will only ever be one or the other answered as far as [Inventory IN] and [Inventory OUT], they will never both have a value on any given row.***

They are then all summed up by the first formula shared up top and fed into {Master Inventory} sheet into cell [Updated Inventory]6 where I also have another cell named [Current Inventory] with the following formula:

=SUM(20 + [Updated Inventory]6) *** Note 20 is my "starting inventory"***

This gives me a dynamic inventory with positive numbers for Inventory IN and negative numbers for Inventory OUT. All I have to do is reconcile every once in a while and change my "Starting Inventory" number to whatever we manually count.

Hope this makes sense and will help others in the future :)

-Brantley

Brantley Brumley · 04/13/20