#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

Genevieve P.

Hi @Joseph Aloysias

How are you moving the rows?

The Move Row action should copy the display value and remove the formula, leaving you just with the end result. Here's more information.

Joseph Aloysias

I use move row automation move the rows from working sheet to archive sheet, but I get the error in the working sheet formula after the rows are moved.

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

Joseph Aloysias

That make sense, thanks you so much!