Is there a way to prevent cell value assigned by formula from changing after sorting?

Jerilyn Hutson

We need to assign a number and letter combination in each row of a sheet. Some rows are considered to be "in a series" and will need the same number but a different letter, i.e. 40001A, 40001B, and 40001C. We have a formula that will assign the letter based on the count of the number, i.e. the first time 40001 is entered in Column X, the letter A will be added to Column Y and when 40001 is entered a second time in Column X, the letter B will be added to Column Y. This is all working well until the a new row is added with an earlier Launch Date or the Launch Date is changed and the rows are sorted by Launch Date thus reordering the rows. This also updates the letters in Column Y. We want to keep the letter as is once it has been added to Column Y as it is directly tied to entries in our CRM database. Is this possible?

Here is the formula to add the letter:

If letter A is added to row 5 and then row 7 which as letter B is sorted above row 5 due to an earlier Launch Date, is it possible to keep the letter A in row 5 and the letter B in row 7 after the sort?

Please let me know if I can provide additional information. Thanks so much! Jerilyn

Toufong Vang

Hi, @Jerilyn Hutson ,

Here's one approach.

Create an auto-number column and count this instead of the ID column. And then use the CHAR() function to find the letter/suffix.

= IF( COUNTIF($[autonum col]$1:[autonum col]@row, < [autonum col]@row) = 0, "", CHAR(64 + COUNTIF($[autonum col]$1:[autonum col]@row, < [autonum col]@row)))

CHAR(65) = A

CHAR(66) = B

... CHAR(90) = Z

This approach works because the auto-number is sequential and the column cannot be modified. It stays with the row regardless of the sort order. Rows created later will always have a higher number in that column.