cross sheet reference and circular reference

Options

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

  • Stefan
    Stefan ✭✭✭✭✭✭
    Options

    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.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!