Hi,

I have a project tracking spreadsheet in which column B is the client ID in
format XYYYY where X is the first letter of the client name and YYYY is a
number created by my accounting software sequentially as follows.

Client 1 = X0001
Client 2 = X0002
Client 3 = Y0003

It doesn't matter what X is, YYYY always rises by 1 as I enter new client
data.

Column C is a proposal ID that I want to generate specific to a client (for
the reason that I don't want clients knowing how many proposals I generate).

The format for any data found in column C is: P/XYYYY/ZZZ whe

P is static, it stands for Proposal and will always appear
XYYYY is the aforementioned Client ID
ZZZ is a 3 digit identifier indicating the proposal number for this
particular client.

So for example...let's say I have 20 records already stored in my table and
I enter two new projects they could look something like:

P/A0001/005 - the 5th proposal made for client A0001
P/F0006/121 - the 121st proposal made for client F0006

Now the question:

1) How can I make it so column B (client ID) is a drop down menu giving me
only the current options available, something I could enter by hand either on
another sheet (preferable) or in an external file.

2) Automatically generate the Proposal Number (column C) based on a) the
client ID and B) the next proposal number in sequence for that particular
client.

My goal is to automate 100% the creation of the proposal ID and have it as a
locked cell.

Can someone point me in the right direction?

Thanks in advance for your assistance.