Hello again,

Hmmm...good question, I assume that I would have one sheet per year. All I
really need from this sheet is that it generates a new proposal number since
some aren't accepted and so I can track the invoice numbers vis a vis
proposals.

My real accounting data is in my accounting program, this is just a quick
reference for fun, redundant really but since it's portable and I don't have
to produce reports it's easy. In the final version I'm going to lock all the
cells that are formulas...the spreadsheet isn't finalized I haven't done it
yet.

My main focus is to learn how to create the input tool and append the final
row of the main invoice table with a new ID and a generated invoice number
based by client. I know there's a lot of redundant information here but it's
by design.

Thank you again.

"PapaDos" wrote:

Are paid invoices ever removed from the sheet ?

You have formulas in your table, you should avoid that for
"accounting/history" data.
Normally, that kind of data should not be tempered with manually (editing,
sorting, etc.).

I am trying to understand how that sheet will be use to give you better
advices...

--
Festina Lente


"neroamdrid" wrote:

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.