Forum Discussion
REF! error on formula cell when new order data is pasted into reference cells
"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?
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 🙏