Forum Discussion
REF! error on formula cell when new order data is pasted into reference cells
I am creating a order tracker for an aerospace manufacturing department and I have run into some issues..
I get the REF! error on formula cell when new order data is pasted into reference cells.
The first REF! Error I need to tackle appears in a simple =a1 formula that extracts data from one sheet (the exported order information copied from the companies system) into my order sheet.
I've tried using =$a$1 but the problem still occurs..
I hope someone can help
Thanks in advance!
9 Replies
All the #REF! error is telling you is that whatever data used to be in the cell A1 has been obliterated and is no longer accessible at that location. What was in the cell before you pasted new content?
If you wish to enter new values without damaging formulas elsewhere, you could use 'Paste values'. Alternatively, you could simply append new data to an existing data table and allow the formulas to extend dynamically.
Hi
Thanks for your help!
To elaborate: the data is drawn directly from the companies DNC factory operating system. It contains delivery requirements in the form of dates, part numbers and operation numbers (the operation changining the material/ part within the production line, I.e; welding subassembily 1 to subassembily 2)
I am trying to build a spreadsheet sheet that extracts the information above and presents it in a easy to understand way.
To do this I would need to export the data from the system into a spreadsheet and then extract the cells data from one file into another. I do this by simply copying the orders with deadline delivery dates within the next two weeks, and then pasting the data into my spreadsheet file from the dedicated export file. I then extract the data from one work sheet to another in the required format (only including the necessary data)I do this by using a =worksheet1!a1 formula. I then use a vlookup to extract information about the operation number and give it a user friendly name, such as weld assembly 1 to assembly 2, instead of a generated number code. I am also experiencing some issues with the vlookup function, but think it best to tackle one problem at a time
"one problem at a time"
Yes, but the first problem might be the overall architecture of your solution. Can you import the "dedicated export file" using Power Query? That would allow you to transform the data before loading it to the workbook.
What version of Excel are you using? I would regard anything before 2021 as obsolete. I do not make friends by holding such opinions, but the recent changes to Excel have been seismic and a modern solution is not even recognisably similar to a traditional solution.
If you are assigning 'friendly product names' to parts, this would normally be achieved using a primary/foreign key combination. That could either be done with an XLOOKUP formula or by joining the export table with your data table within PowerQuery. A sample data file would be useful.
Riny_van_Eekelen Your thoughts on PQ solutions?
Since you are using data on different worksheets, you must mention the worksheet name before the range such as this: =Sheet1!A1
Sheet1 is the worksheet name then ! before the cell or range nameI forgot to mention that in the above, apologies
My formula does however contain the sheet name.
I was thinking maybe I need to use the "INDIRECT " function in my formula 🤔
Do you think that would overcome the issue?
