# Excel generate sequential number



## JudeW (Sep 4, 2003)

Hello...I'm an office coordinator that does a lot of "purchasing" for office supplies. I have created a purchase order form that I fill out using a VLOOKUP for products that I order from a specific vendor.

What I'd like to do is: create a new purchase order, have it generate a unique sequential number, ie, ONT200301, ONT200302, etc., complete the form, and print it.

Since I'm fairly new to excel, I need simple instructions on how the sequential purchase number can be created. I've seen some solutions, but they're so "techie" I'm lost!

Thanks for your help!

Jude


----------



## Anne Troy (Feb 14, 1999)

There's a free download at www.theofficeexperts.com that is called MyInvoicing. It shows a new number each time... You can likely steal the code from there. If you can't make the code do your ONT..., then just paste that part of the code here and we should be able to get it to work for ya.


----------



## JudeW (Sep 4, 2003)

Thank you! This should help. Maybe I can just change Customer (sheet) to Vendor. And Invoice to Purchase Order. Thanks again for your quick reply!


----------



## CastleHeart (May 4, 2002)

There are a lot of ways of doing this. Some easy and some intricate. They all depend on the different expectations of the program you have written... things like whether you wish to save each P.O. and when do you want it to create the next number? Upon opening the program? - closing? - printing? I don't know your circumstances so I'll just pass along a simple one we set up some time back.

We had a P.O. form that we used. On the page in cells out of sight (_ I'll just say_ *X1*_ and_* X2* ) were:

X1 was the last P.O. number used
X2 was the formula =X1+1

On the P.O. form page we created a macro assigned to a  *NEW P.O.* button.

When that button was pushed, a macro deleted all the appropriate cells in the P.O. form so as to clear it out and then went to the cell *X2* copied it, and then _ "paste values" _ back into *X1*. The *P.O.# CELL* in the P.O. form had the formula =X1

When this program was opened - it displayed the last P.O. written - for reference sake. Pushing the button cleared it for a new P.O. and indexed to the next P.O. number.

It worked fine. There were draw backs. Like hit the New P.O. BUTTON twice and you went two numbers ahead. We later improved upon it with safeguards to prevent that - went to a dropdown list to choose the P.O. series - and had the macro save each P.O. number along with it's date written in a list on a separate sheet as a running check. You could quickly look and see when one was written. We later added different series of P.O. numbers and you were able to select the series you needed to work from.

The ultimate product works like a charm.

- Castleheart


----------



## JudeW (Sep 4, 2003)

Thanks Castleheart! I don't need to print out each PO...just keep a list with PO number, Vendor, Total of PO, Date Ordered, and Date Received. I've just been typing all that in a "log" sheet after I've phoned in the order to the vendor. As for when to create new number...probably upon printing the PO. I'll probably only be placing 2 or 3 orders a day.


----------



## CastleHeart (May 4, 2002)

No Sweat! It will keep the log for you. Easiest thing may be for you to send me an address via private messaging or Email me and I will send you an example xls


- C


----------



## Anne Troy (Feb 14, 1999)

Hey CastleHeart...go make some furniture or something would ya?

ROFL!!

Anyway....the file I pointed Jude to does almost exactly what he is looking for already. Did you check it out? All he needs to do is change the text from Invoice to PO, etc...


----------



## JudeW (Sep 4, 2003)

Ummmm....by the way..."he" is a "she"!!

I've been working on changing the text on the myinvoice.xls. Looks like it's going to work, but I need to delete some of the info I don't need...like past due, etc.

You guys are great!

Jude


----------



## Anne Troy (Feb 14, 1999)

Sorry, Jude.
Many people have called me a he too. Until I posted my avatar.
You need one?


----------



## JudeW (Sep 4, 2003)

Nah! This makes life more interesting!

Okay, I think I nearly have the myinvoicing.xls nearly ready. Can I change the file name to reflect purchase order instead of invoicing? How about adding new worksheets...will that mess anything up. (I have a sheet for printer cartridges: what kind and where located). 

Thanks once again!

Jude


----------



## Anne Troy (Feb 14, 1999)

I don't see any problem changing file name. I don't think it does anything...
Adding worksheets is fine too. If you can do a little bit of VBA, you can also change the names of the existing worksheets...

Work on a copy of the file....


----------



## JudeW (Sep 4, 2003)

I can only do VERY basic tasks in Excel. No VBA! It took me a week to figure out the VLOOKUP...now it looks like I won't need it! But that's okay...this is MUCH better! I've decided not to use the ONT in the actual PO number. Our company has two branches: Ontario and Losi...so ONT and LOS was to distinguish between the two. I'll just verbally give ONT or LOS to the order taker so it will be on the invoice and I'll know which branch to GL when I pay the bill!


----------



## Anne Troy (Feb 14, 1999)

Ok. Let us know if you have any other probs. Me and my coder created it.


----------



## JudeW (Sep 4, 2003)

Good Job! And Thank You VERY much!


----------



## Anne Troy (Feb 14, 1999)

Sure.


----------



## CastleHeart (May 4, 2002)

Hey....can I talk now ?

I guess ya'll got it figured out without my help. That's OK.... I was putting out fires around here.

Hey Jude (_ hmmm... that sounds familiar..._)- don't give up on having *ONT* in there associated with the P.O. if you want it! 
I took _(the finely crafted)_ program, brought to you by Dreamboat, and had it add that next to the P.O. numbers. You enter it as part of your invoice information.

It still saves them according to the number serquence but adds that prefix to the PRINTED INVOICE and in the SALES TAB for your reference later. Tis a snap!

Besides... I had to do something while you all were chatting !

- Castleheart 

*spelling 101*


----------



## Anne Troy (Feb 14, 1999)

Fix your bb code, CH. It makes you look like an ammacher. LOL!!


----------



## JudeW (Sep 4, 2003)

Thanks, Castleheart! Can't say I really know what you're talking about...LOL. But thanks! I'm getting too much info today! Shoulders are getting wet from the brains leaking out of my ears! I'll read it again tomorrow and see if it makes more since then!


----------



## CastleHeart (May 4, 2002)

idid

*!!!!!!! Dyslexics UNTIE !!!!!!!!*


----------

