Auto numbering - Inserting rows without changing numbers

[Kayaness]

Hi all,

I have a spreadsheet with an auto number (what we call a Forecast Number) on column A that only appears if data is entered into column B of the same row. I'm using the following formula:

=IF(ISBLANK(B27),"",ROW() -2)

Our Forecast Number is used to keep track of specific projects entered into the spreadsheet and needs to remain constant.
The problem I have is that by using this formula, when a new row is inserted in between existing rows of data (which happens as a result of changes to project requirements), the Forecast Number of the rows below changes accordingly.

What I'd like to do is set something in place where the auto numbering happens exactly as I've said above, but the number is unchangeable once it's been allocated, and that any new rows inserted will be given the same Forecast Number as the one above it, but with a letter at the end (a, b, c, etc).

I'm presuming this could only be done using VBA, but I have no idea how I would write the code.

Any help I could get on this would be greatly appreciated.

[dilipandey]

Hi Kayaness,

I am not totally sure but you can use count functions (count / counta / countblank etc) instead of ROW function which you have in your formula.. Try looking at these functions if this appeals you.. in case of any issues, please post a sample file so that i can help you out. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>