Dynamic Creation of Invoice number
 

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.

Dynamic Creation of Invoice number
 

If I were to set up such a system, I would probably use a separate worksheet
as a register for the client proposals. Then, any entry with a P preceding
the standard document number would query the register for last number used
and result in the assignment of the new number and register update. This
would require that a register be set up for all the clients that you might
issure a proposal to. Is it worth it?

"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.

Dynamic Creation of Invoice number
 

On Oct 9, 2:16 pm, JLGWhiz wrote:
If I were to set up such a system, I would probably use a separate worksheet
as a register for the client proposals. Then, any entry with a P preceding
the standard document number would query the register for last number used
and result in the assignment of the new number and register update. This
would require that a register be set up for all the clients that you might
issure a proposal to. Is it worth it?


I agree with JLG. When you start to encounter these sort of issues,
you should ask yourself if you should be using a database instead of a
spreadsheet. If the problem is just that you don't want your customers
knowing how much business you are doing - consider writing a function
that will generate a 'seemingly' random number (based on the customer
ID) OR code your proposals with some sort of a time stamp - one so
granular it won't be duplicated (YYYY.MM.DD.HH.SS for example)

AR

Dynamic Creation of Invoice number
 

Assuming that CLIENT_TABLE is your clients ID list, CLIENT_ID is the cell
where you enter the client ID (dropdown) and PROPOSAL is where you want the
proposal ID to appear.

1) Use Data Validation on CLIENT_ID, pointing to CLIENT_TABLE.
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" )

--
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.

Dynamic Creation of Invoice number
 

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 :)

"JLGWhiz" wrote:

If I were to set up such a system, I would probably use a separate worksheet
as a register for the client proposals. Then, any entry with a P preceding
the standard document number would query the register for last number used
and result in the assignment of the new number and register update. This
would require that a register be set up for all the clients that you might
issure a proposal to. Is it worth it?

"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.

Dynamic Creation of Invoice number
 

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.

Dynamic Creation of Invoice number
 

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.

Dynamic Creation of Invoice number
 

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.

Dynamic Creation of Invoice number
 

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 :)

"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.

Dynamic Creation of Invoice number
 

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.

Dynamic Creation of Invoice number
 

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

perhaps.

Tom Ogilvy


"neroamdrid" wrote in message
...

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.

Dynamic Creation of Invoice number
 

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

I've uploaded my table with embedded comments he
http://www.dslreports.com/forum/rema...58266#17059103

Thank you

"PapaDos" wrote:

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

I've uploaded my table with embedded comments he
http://www.dslreports.com/forum/rema...58266#17059103

Thank you

"PapaDos" wrote:

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

The address I gave you is "auto-cleaning" every night, so I hope you were
able to retrieve my example before it was deleted. If not, let me know, we
will try again.

I was unable to retrieve yours, it was already deleted.

I hope my example was useful to you...

Regards,
Luc.
--
Festina Lente


"neroamdrid" wrote:

I've uploaded my table with embedded comments he
http://www.dslreports.com/forum/rema...58266#17059103

Thank you

"PapaDos" wrote:

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

Yeah I was wondering about that :)

Here's the link - http://www.dslreports.com/forum/remark,17061545

"PapaDos" wrote:

The address I gave you is "auto-cleaning" every night, so I hope you were
able to retrieve my example before it was deleted. If not, let me know, we
will try again.

I was unable to retrieve yours, it was already deleted.

I hope my example was useful to you...

Regards,
Luc.
--
Festina Lente


"neroamdrid" wrote:

I've uploaded my table with embedded comments he
http://www.dslreports.com/forum/rema...58266#17059103

Thank you

"PapaDos" wrote:

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

Yeah I was wondering about that :)

Here's the link - http://www.dslreports.com/forum/remark,17061545

"PapaDos" wrote:

The address I gave you is "auto-cleaning" every night, so I hope you were
able to retrieve my example before it was deleted. If not, let me know, we
will try again.

I was unable to retrieve yours, it was already deleted.

I hope my example was useful to you...

Regards,
Luc.
--
Festina Lente


"neroamdrid" wrote:

I've uploaded my table with embedded comments he
http://www.dslreports.com/forum/rema...58266#17059103

Thank you

"PapaDos" wrote:

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

I keep getting an error while trying to post so here goes again:

Here's the link - http://www.dslreports.com/forum/remark,17061545

"PapaDos" wrote:

The address I gave you is "auto-cleaning" every night, so I hope you were
able to retrieve my example before it was deleted. If not, let me know, we
will try again.

I was unable to retrieve yours, it was already deleted.

I hope my example was useful to you...

Regards,
Luc.
--
Festina Lente


"neroamdrid" wrote:

I've uploaded my table with embedded comments he
http://www.dslreports.com/forum/rema...58266#17059103

Thank you

"PapaDos" wrote:

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

LOL
Please don't use the forums at DSLreports.com as a way to exchange files !
If you want help from members there, fine, but don't just post a file like
that, explain the problem...

Please go back there and ask the moderators to move it to the "/dev/nul" or
to delete it, if you don't intend to ask help from members there about this
subject.

The forum I used to pass you my file is self deleting daily, but I probably
shouldn't have use it for that anyway...

I will take a look at your file tonight and post back.

Thanks,
Luc.

--
Festina Lente


"neroamdrid" wrote:

I keep getting an error while trying to post so here goes again:

Here's the link - http://www.dslreports.com/forum/remark,17061545

"PapaDos" wrote:

The address I gave you is "auto-cleaning" every night, so I hope you were
able to retrieve my example before it was deleted. If not, let me know, we
will try again.

I was unable to retrieve yours, it was already deleted.

I hope my example was useful to you...

Regards,
Luc.
--
Festina Lente


"neroamdrid" wrote:

I've uploaded my table with embedded comments he
http://www.dslreports.com/forum/rema...58266#17059103

Thank you

"PapaDos" wrote:

I created a simple example for you.
You can get the file at
http://www.dslreports.com/forum/remark,17058266

Don't be shy with your questions...
--
Festina Lente


"neroamdrid" wrote:

There's definitely no reason to apologize :)

I've never worked with macros and the building of new rows is something I
have no idea where to begin with.

Are there any resources you can point me to? If I hit a major snag hopefully
it can be at 80% done and you can help me out with the finishing touches.

I'm also not sure on "unique top row"

Thanks.

"PapaDos" wrote:

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

Dynamic Creation of Invoice number
 

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.

Dynamic Creation of Invoice number
 

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.

Dynamic Creation of Invoice number
 

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.