Auto ID based on Prefix and suffix

Hello

sorry, ive read a few posts on Auto ID but i still cant get to do what i want.

I want to create Auto ID's based on user defined prefix and suffix in excel.
preferably it should operate like a form, making it quick and easy to generate consecutive ID's.

eg.
Prefix-AutoID-Suffix
ABCD-0000-xx

The best way to describe its use is as labeling buckets of materials
The Prefix says what material the bucket is carrying
Auto ID gives a unique ID (incremental) when more buckets are carrying the same material
The Suffix gives the quantity in the bucket, which is measured on site.

Like a form the user only has to define a prefix then input a new suffix to create the next ID.

I thought it was quite simple, but its turned out much harder than i thought.

To make it simple, lets say, the user labels all buckets carrying the same material in one go, not changing materials frequently, hence the macro doesn't have to look up what was the last ID used on a particular prefix. It can just increase the Auto ID by 1, given a new quantity. The user then manually changes the prefix for the next (different) material and the count starts again. If the user needs to go back to a material he's already done, he can look up the last ID used manually and start from there.

Infact it was the thought of the last paragraph that seems to have confused me now. getting rid of the need to look up the last ID seems to have made it harder than easier :-s

Any input will be much appreciated.

Thank you

Hey thanx alot
Yip that did the trick for the first bit...
I could use the same function to add the xx at the end, and the user can just manually edit xx to the quantity desired.

It works, i guess that's what counts.
Thank you, that was quite simple and effective.

any hints on how i could further improve it? so one can use just 2 input cells rather than making whole columns for the inputs?

Thanks again