#REF in the formula when I move data to archive sheet
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.
Joseph Aloysias| Solutions Lead
vSaaS Global
Book a Meeting
Phone: +91-8148459084
Best Answer
-
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
Answers
-
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.
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
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.
Joseph Aloysias| Solutions Lead
vSaaS Global
Book a Meeting
Phone: +91-8148459084 -
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
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao!👋 | Global Discussions
-
That make sense, thanks you so much!
Joseph Aloysias| Solutions Lead
vSaaS Global
Book a Meeting
Phone: +91-8148459084
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.6K Get Help
- 435 Global Discussions
- 152 Industry Talk
- 495 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I am working in a separate sheet for calculating metrics. I want to count the number of items in a referenced date range in another sheet that meet certain year criteria. For example, I want to find t…
- I have a column that is a contact list where a single or multiple staff members can be selected to be scheduled. I'm trying to create a formula that counts how many times each individual's names are l…
- I have two columns I am using for this formula: "Date Received" and "Days Duration (Received to Listed)". I want the formula below to look at any items in the Date Received column between 1/1/25 and 1…