COUNTIF formula changing when adding new data to a table.

[oldraper]

Hi there, I really hope someone can help me, I'm in way over my head and have only just got started.

I have a table of data, where one column lists a selection of suppliers. I have then built a helper column that gives each instance of the supplier being used a unique reference code. ie. SupplierA 1, SupplierA 2, using the COUNTIF FUNCTION.This is the helper cell code for row 12 for example.

Please Login or Register  to view this content.

This all works fine. HOWEVER if i try and add a new item to the table on Row (specifically when i fill in the supplier column, lets say in our example this would be previously blank cell E13) , the formula in the helper column for row12 prangs out, and mimics the helper cell below it, becoming

Please Login or Register  to view this content.

And if i try to add more, they ALL mimic one another. The list was populated with dummy data before I added the formulas which is why i hadnt noticed the problem until now.

I don't know if I've explained that well enough at all.Please take a look at the attached, on the 'Product List' tab, and try and enter a new supplier in E13, E14 etc, and see how the Column F prangs out.

Thanks you for taking the time! I'm sure its simple if you know how, but trying to google a solution when none of the terms make sense to me is like trying to unlock a padlock with a banana!

Kind regards
Ollystock sheet.xlsx

[ExcelHelper2]

I find your problem is not that bad. Excel tries to be clever, but it isn't. It notices that in the column F is an inconsistent formula (the green marker in the upper left corner. If you click the cell, it gives you the option to restore the calculated column formula. The other option would be just typing all of your values and at the end go to cell F2 and hoover over the right bottom corner and pull down the formula to the whole column.