I would like to know how to create a formula that will accomplish the
following without using a macro, if possible.

Use: I aim to use this worksheet to automatically create ESTIMATE numbers
sequentially without duplication. The file will be located on a server on a
LAN were multiple users can access the file. If possible, I would like to
share the file as well.

The number I would like to generate automatically would include text and
numerical information as follows: "ESTYYYYNNNN" ex. EST2006-0001
Prefix - "EST"
Current Year - "2006"
Hyphen seperating year and number
Number - "xxxx" where the first number would be 0001 the next number 0002
and so forth.

This field should be automatic so that when a new row is added, the number
is automatically created. The user would not be able to edit or delete this
number. Is that possible?

Thank you in advance for your assistance and comments on the above.

Kind regards,
Sollstar

="EST2006-" & text(countA(A2:A1000),"0000")

Lock the cell and protect the sheet.

--
Regards,
Tom Ogilvy


"sollstar" wrote:

I would like to know how to create a formula that will accomplish the
following without using a macro, if possible.

Use: I aim to use this worksheet to automatically create ESTIMATE numbers
sequentially without duplication. The file will be located on a server on a
LAN were multiple users can access the file. If possible, I would like to
share the file as well.

The number I would like to generate automatically would include text and
numerical information as follows: "ESTYYYYNNNN" ex. EST2006-0001
Prefix - "EST"
Current Year - "2006"
Hyphen seperating year and number
Number - "xxxx" where the first number would be 0001 the next number 0002
and so forth.

This field should be automatic so that when a new row is added, the number
is automatically created. The user would not be able to edit or delete this
number. Is that possible?

Thank you in advance for your assistance and comments on the above.

Kind regards,
Sollstar

Dear Tom,

Thank you for your help. I had to adjust the forumula a little as it was
summing all the records in column "A" therefore the estimate number kept on
changing when I added a new row. The formula with the minor adjustment seems
to be doing the job:

=IF(A2=0,"","EST2006-"&TEXT(COUNTA(A$2:A2),"0000"))

thank you for getting me on my way.

Kind regards,
Sollstar

"Tom Ogilvy" wrote:

="EST2006-" & text(countA(A2:A1000),"0000")

Lock the cell and protect the sheet.

--
Regards,
Tom Ogilvy


"sollstar" wrote:

I would like to know how to create a formula that will accomplish the
following without using a macro, if possible.

Use: I aim to use this worksheet to automatically create ESTIMATE numbers
sequentially without duplication. The file will be located on a server on a
LAN were multiple users can access the file. If possible, I would like to
share the file as well.

The number I would like to generate automatically would include text and
numerical information as follows: "ESTYYYYNNNN" ex. EST2006-0001
Prefix - "EST"
Current Year - "2006"
Hyphen seperating year and number
Number - "xxxx" where the first number would be 0001 the next number 0002
and so forth.

This field should be automatic so that when a new row is added, the number
is automatically created. The user would not be able to edit or delete this
number. Is that possible?

Thank you in advance for your assistance and comments on the above.

Kind regards,
Sollstar