#REF in the formula when I move data to archive sheet

Joseph Aloysias

When I transfer completed orders from the working sheet to the archive sheet, I encounter a formula error in calculating the final stock.

Before moving the data:

=[Initial Stock]@row - SUMIFS([Total Quantity Requested]$1:[Total Quantity Requested]@row, Collected$1:Collected@row, 1, [Item_ID]$1:[Item_ID]@row, [Item_ID]@row)

After move the data to Archive sheet:

=[Initial Stock]@row - SUMIFS(#REF:[Total Quantity Requested]@row, #REF:Collected@row, 1, #REF:[Item_ID]@row, [Item_ID]@row)

Please help me with a workaround.

Genevieve P.

Hi @Joseph Aloysias

Thank you for clarifying!

It sounds like you may be moving the original Row 1 from your current sheet into the archive sheet. When this happens, the reference is deleted since you're referencing that specific row:

=[Initial Stock]@row - SUMIFS([Total Quantity Requested]$1:[Total Quantity Requested]@row, Collected$1:Collected@row, 1, [Item_ID]$1:[Item_ID]@row, [Item_ID]@row)

This is why you see the error #REF

You'll want to make sure Row 1 in your current sheet is a row that will never be moved, so you can keep the reference. Does that make sense?

Cheers,

Genevieve