Sorry, I made a mistake while editing my formula from ranges to names.
Replace CLIENT_TABLE with PROPOSAL_TABLE, which is the table of proposals ID
already existing.

I think you should use a unique top row to "build" the new entries and use a
button to call a macro that would copy those values to the end of your
PROPOSAL_TABLE. The macro could take care of unlocking, copying and relocking
the table's rows.
--
Festina Lente


"neroamdrid" wrote:


1) Use Data Validation on CLIENT_ID, pointing to CLIENT_TABLE.

Ok...following some other instructions that I located on this board I
created a second worksheet in this book and I called it "lists".

Right now I'm just testing with clients, R0001 and V0002 and so I created a
dropdown menu using Data Validation to include these two options. I then
pasted this into the Client ID column and it offers me the two values that
I've given.

You want actions to happen "automatically", what triggers those actions ? A
button ? A key sequence assigned to a macro ?

Sorry for the lack of clarity...here's the scoop.

This is what's already happening:
1) I create a new row and I generate an ID for that row using =ROW()
2) I select which client I want from the drop down (assume V0002 for this
example)

This is the final step which I need to make happen:
3) Based on choosing V0002 I want to generate P/V0002/07 (since I already
have 6 manually entered proposals)

What else is on that "new line" ? How is it entered ? Manually ?

Here's the additional info, although none of it is tied to this formula..

Proposal date - entered manually
Description - entered manually
Cost - entered manually
Date approved - entered manually
Deposit % - entered manually (varies by client & project)
Depost total - generated by Cost-Dep%
VAT tax - generated when cost is entered
Other tax - generated when cost is entered
Total to invoice - generated when cost is entered
Invoice number - entered by hand (generated by my accounting software)
Paid - True/false field that changes project color to indicate state as well
as total
Invoice date - Entered by hand

2) Use that array formula (Ctrl-Alt-Enter), in PROPOSAL:

=" P/" & CLIENT_ID & "/" & TEXT( MAX( ( LEFT( CLIENT_TABLE, 8 ) = "P/" &
CLIENT_ID & "/" ) * ( RIGHT( CLIENT_TABLE, 3 ) ) ) + 1, "000" )

Here's what need to happen, and since I'm new to the more complicated
functions I'll reason this out since I'm a little lost.

In plain english...

Take the output of the client ID file, wrap the P/ before it, and add the 3
digit code indicating how many proposals I've made for that client.

I need to then store that final 3 digit number somewhere so that the next
time I create a proposal number for this same client I can sum +1 to it.

Thanks again :)



=" P/" & CLIENT_ID & "/" & TEXT( MAX( ( LEFT( CLIENT_TABLE, 8 ) = "P/" &
CLIENT_ID & "/" ) * ( RIGHT( CLIENT_TABLE, 3 ) ) ) + 1, "000" )

Now here

"PapaDos" wrote:

We need more details of your design.

You want actions to happen "automatically", what triggers those actions ? A
button ? A key sequence assigned to a macro ?

What else is on that "new line" ? How is it entered ? Manually ?

--
Festina Lente


"neroamdrid" wrote:


Well that's the good news...maintaining the Client table is a piece of
cake...most of my work comes from 3 clients with a rare sporadic new client
which is why I'm going this route. It's rather unforeseeable that I would hit
even 15 clients in the next year, most of my business is repeat.

The other thing is, and i know this is never a valid line of reasoning, but
the rest of the spreadsheet is complete, this is the last task I need to
complete before I can lock it up and put it in use so I'm reticent to go
another route.

Given that maintaining the client table is a non-issue, is there a solution
for me?

I think there might have been some confusion in how I worded my first post
looking back now.

What I want to be able to do is...

insert new row
select client ID from drop down
and by virtue of selecting the client, have the proposal number generated on
the fly based on Client ID and Proposal ID (which would be derived from the
last used Proposal +1).

Thanks to all of you for such quick replies :)
"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.