Still, your table is useless if you can't trust the information in there.
Locking cells with formulas is not making those cells safer.

The problem is that you want to play with the table, sorting, direct
editing, etc.

For example, with the table you posted at DSLR, if you sort the table on the
"Cliente" column, your ID column is screwing up. The rest of your current
formulas are ok as they are now when you sort, but if you ever change them or
make a mistake in your manipulations, they can become unreliable.

What I would do is create an "editing" area (1 row) where you build new rows
and edit existing ones. The table would contain only values (no formulas) and
the insertion/replacement in the table would be controled by macros. My
sample file was doing that for new rows. It would be relatively simple to
modify to add the "extracting/editing" capability.

If you prefer to work directly in the table, the formula to build the
proposal number has to be put in a cell out of your table and its value
copied (by maccro or manually) in the new row. Putting that formula directly
into the table is unreliable, if at all possible.

In your sample file, C9 is looking for a named reference "client_current"
that you haven't define. It shoul be pointing to $B$9. The formula also fails
because you have entries in that column not respecting the format you
specified (blanks and "-------").

You need to document yourself about buttons and macros. Look for "ActiveX
control" in the Excel Help system...

Let me know how it evolves and feel free to ask questions...

Regards,
Luc.
--
Festina Lente


"neroamdrid" wrote:

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.