REF! error on formula cell when new order data is pasted into reference cells

Occasional Contributor

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

Hi @Aerospacemanager 

 

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 name

Hi @Jihad Al-Jarady

I 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?

@Aerospacemanager 

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.

@Peter Bartholomew 

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

@Aerospacemanager 

"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?

 

@Peter Bartholomew

Very likely that PQ will be an option. Personally, I'm a big fan of PQ, as many other contributors out here. But I agree that we would need a sample of the exported data, as well as the reports/extracts required.

@Riny_van_Eekelen @Peter Bartholomew 

Hi both,

Thanks for your replies. I will see if I can get any non-sensitive samples and find out what edition on excel my company uses when in the office tomorrow 

 

Once again, Thank you

@Peter Bartholomew 

 

Hi Peter, 

 

Turns out my company uses the 2016 version (*face palm*)

 

I have taken a picture of what I'm trying to do. I've worked out that using a data table seems to fix the import issue. Now I'm attempting to rename the operation codes to something more user friendly.

 

REF the picture below:

 

On sheet 1 I have a table that links to the data table on sheet 2. The information displayed on said table is the deliver date, the quantity,  the part number, the stock quantity. I am trying to create a user friendly current/ next operation name associated with the table on sheet2. I do this by using an =if(sheet2!E4<6006,-1,1) and then multiply the answer by the curr op/ next op displayed on the data table work out one of two operation orders that the parts follow. The table created on the right of the worksheet links the operation code to the user friendly name associated with it. 

 

I am trying to work put a way that I can get the cell in sheet1 to look up the calculated operation number and then read it against the table, then find and display the user friendly name. 

 

Any ideas would be much appreciated  

 

16690534152502364759178540106807.jpg

all sorted, used the =vlookup function to filter the needed data 

16690590764065865744005700264607.jpg

@Aerospacemanager