cross sheet reference and circular reference
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.
Answers

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
Smartsheet Consulting, Solution Building, Training and Support.
Projects for Processes and for People.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!