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

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.

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

On Oct 9, 2:16 pm, JLGWhiz <JLGW...@discussions.microsoft.com> 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

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

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

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

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

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

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

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