Formula referring to wrong cell/row after sorting list?

I have an issue with a pricelist that I use.

Basically the list contains several columns with manufacturer, supplier, names, prices etc.
The prices is calculated by looking at the manufacturer on the same row and matching this against a discount table. So the prices is updated easily.
This works fine.

But from time to time there is new products coming in, and I add them on a new row. And then I sort the whole list by manufacturer. And here the problem starts. Suddenly here and there the formula referring to the manufacturer on the same row, is no long referring to the manufacturer on the same row. But instead referring to a cell what seems randomly far away, resulting in calculating the wrong price.

I am not sure what this problem is called but if someone know what its called and can tell me how to fix this I would be very happy.

I am assuming these formulae to which you refer are simple sheet references, of the type =Sheeet2!A1 - would this be right? If so, then you need to use lookup formulae instead based on each record's unique ID.

If you want more specific help, upload a sample workbook showing the issue.

Attach a sample workbook so we can assess the context.

If you are referring to a static cell whose position might change, you are always going to run into trouble, but there is most likely a way round it. However, I'm not into guessing games, so ... Over to you. Workbook, please!

Will wait for the sample workbook - thanks.

I should have added that switching to structured Excel tables often obviates these issues.

Is this a Google Sheets query? If not, please attach the workbook directly here (instructions at the top of the page).

No its excel just linket from dropbox
I uploaded the file now but when clicking the paperclip in here to attach I am not seeing it.

You did it, but the paper clip does not work, which is why I directed you to the instructions at the top of the page. I'll have a look.

Can you tell us of one specific example for us to focus on - one where you see the problem? Thanks. Which row?

On sheet "ProduktDB" the column J has the formula reffering to column B on the same row. It happens on all row. But it seem that when I add new manufactureres and sort the list that the referring cell is no longer on same row.

By example cell J4 is correct. It now referring to cell B4.
But when you look at J7 you see a #N/A now and that is because its not referring to B7 but B565 which is wrong. Changing this to B7 the formula is fixed and correct.
Why this changed from B7 to B565 is mostly because i deleted several hundred rows to compress the file. And when sorting it referred to the wrong cell. But this seems to also happen when adding new products to the list and then sorting the list. The referring cell is then moved resulting in wrong result due to wrong referring cell. not sure if this makes any sense?

Not sure if $ locking the referring cell would work here, since I often add multiple items at the same time, and then just dragging/copying the cell formula to all other. And if I had $ the cell they would all refer to wrong cell.

There seem to be several issues at play here, not least the fact that some formulae in those columns have been overwritten with manual entries!

My advice would be to replace your hand-made tables with proper structured tables and see if that makes a difference. You WILL need to update the formula to take advantage of structured references, but once that is done, you should be OK.

Not sure I follow, I'm not that good with Excel So an example would be good.
The formula changes depending on manufacturer, this is due to how we get the pricing. Sometimes we get our prices, but sometimes we get recommended price, so there isn't one formula fits all.

Thanks

All the more reason for using structured tables.

Essentially, what you need to do is first switch of autofilters, then select the entire table, then Insert | table ... My table has headers. This converts the range to a table.

Then you need to change any reference in the top row of formulas to cells within the table by adding a structured table reference (easily done).

e.g. if you have a reference to F2 in a formula and F2 is part of the table, select F2 in the formula and click on F2, and it will change to a structured reference.

Once this is done, copy all down and thereafter it should pretty much look after itself.

Attached is an example of what I mean. I don't have time to troubleshoot this or check for errors, but it illustrates what I explained in my last post. Over to you now!

Have a look here: https://www.xelplus.com/excel-hash-sign-formulas/

No - it's for info, really. It was new to me, too, but I can see many uses for it.

Thanks for letting us know.