Inserting a row in the middle of a table corupting original formula

[How How]

Hi,
I have attached a test file of what I'm trying to do. Basically the user will add a tag ID in col A....As there could be multiple ID tags I had to make a formula in col B to add a sequential letter (Eg 101 a, 102 a, 103 a, 103 b, 103 c, 104 a). The problem is if a later entry (new row) into 103 is needed, it upsets the formula in col B. Note Col B will be PW protected.
Test log.xlsm

[FDibbins]

Probably the easiest way yo overcome this, is to go to the cell in B above where the row was inserted, and copy the formula down again. You could probably write a small macro to do that for you

[Ron Coderre]

You've implemented an Excel Table, but have not exploited its native capabilities by using structured table references.

Using your posted workbook...
Put this formula in B2 and copy it to the bottom of your table.

Please Login or Register  to view this content.

Now, if you insert or delete a table row, the Excel Table will accommodate that change by preserving the formula.

Note: I think if I put my mind to it, I could come up with a more elegant formula...but, that one seems to work.
EDITED TO INCLUDE THIS ADDITIONAL COMMENT: I tried to shorten the formula I posted, but didn't have any luck.

Does that help?

[How How]

Yup, That will do the trik.....Thank You!!