Autonumber when choosing text

[MachielBrink]

Hi all,

I would like to generate a unique number(in column A, worksheet BREEAM) when choosing an item from my dropdown in colomn C(see example).
I tried numerous things but with al the options I've tried it works fine untill i'm going to use the sort options.

So what I would like to do is:
- Generate a unique number when there's text in Column C.
- This unique number needs to be generated automatically and checks which is the highest number in column A + 1
- It shouldn't renumber al the items when using the sorting options.

Can anybody point me in the right direction?

Thanks in advance and Kind Regards
Machiel

[Sindhus]

how about

Please Login or Register  to view this content.

[MachielBrink]

Hi Sindhus,

Thanks for your answer, I tried that one and tried a few similar options I came up with myself.
But somehow the numbering is incorrect and changes constantly.

Do you or somebody else have another idea.
I think we need to use a macro for this one.

[Sindhus]

Could you specific examples?

[Marcol]

Please Login or Register  to view this content.

I don't think you can do this without copying the result and using Paste special > Values

[MachielBrink]

Could you specific examples?

Not sure what kind of examples you want but i've added your suggested solution to my worksheet.
You will see the problem when choosing something from column C.

I tried variations on that same IF statement.
Tried using ROW function, that works fine until you use sort function from excel.

[Sindhus]

Oh got it. You are sorting your sheet after generating the number rite? Then you use paste special as Marcol has suggested. Or, write the value using a macro.

May be a code like this.

Please Login or Register  to view this content.