Dynamic Creation of Invoice number

G

Guest

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

Guest

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?
 
D

ducky

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?
Click to expand...

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
 
G

Guest

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" )
 
G

Guest

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

Guest

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

Guest

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 ?
 
G

Guest

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

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 ?
Click to expand...

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 ?
Click to expand...

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:
Click to expand...
=" P/" & CLIENT_ID & "/" & TEXT( MAX( ( LEFT( CLIENT_TABLE, 8 ) = "P/" &
CLIENT_ID & "/" ) * ( RIGHT( CLIENT_TABLE, 3 ) ) ) + 1, "000" )
Click to expand...

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
 
G

Guest

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

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 ?
Click to expand...

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 ?
Click to expand...

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:
Click to expand...
=" P/" & CLIENT_ID & "/" & TEXT( MAX( ( LEFT( CLIENT_TABLE, 8 ) = "P/" &
CLIENT_ID & "/" ) * ( RIGHT( CLIENT_TABLE, 3 ) ) ) + 1, "000" )
Click to expand...

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

Guest

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

Guest

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

Guest

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

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can Excel sequentially number each new document? 1
Still not flying 13
Run-time error 75 Path/File access error 2
Paste to next row if cells are not empty 2
Find dupes, list in MsgBox with cell.address convert ws wide code toone sheet 6
data validation defaults 2
Sub runs, but gives error when called. 7
Concatenate Date and Number field for Append Query 0

Top