cross sheet reference and circular reference

JANET WEEKS

This is the formula that is giving me the Circular reference error

=SUM([Inventory Quantity]@row - (SUMIFS({Order Qty}, {Auto ID Number}, [Auto UNIT ID]@row)))

What I need to do is take the current value in the [Inventory Quantity] and subtract the Order Quantity on another sheet. So if you have a value of 1 in the [Inventory Quantity] cell and there a value of 1 in the {Order Qty} then it should return a value of 0.

Stefan

Hi @JANET WEEKS,

#CIRCULAR REFERENCE

Cause

The formula references itself. The circular reference may be direct where the reference is in the formula text itself, or indirect where this formula references a cell which then references back to this cell.

Resolution

Determine which reference is circular. Indirect references can be many levels deep. Sometimes it is easiest to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination will help you see which reference is ultimately circular.

From your formula I can only guess, that in the ranges in the other sheet there are references to the "Inventory Quantity" column.

Maybe you can provide screenshots?

Hope this helps

Stefan

Mike Wilday

Yes, Stefan's prognosis is correct. You want to be sure your summary field is not being included in the column you're referencing. Try summing the data in a sheet summary field instead of in the sheet itself.

https://www.youtube.com/watch?v=UleJxbmDV1U

https://help.smartsheet.com/learning-track/smartsheet-intermediate/sheet-summary