Excel for automatically generating PO numbers and using a macro to populate template

Hi all,

I am looking to set up a new PO system on excel. There is a column on the spreadsheet for Yes/No/Blank if the PO's on individual lines have been approved (I have protected this column on the spreadsheet for when this process is in place). My question is twofold,

1. Is it possible to get excel to generate a sequential PO number based on when the column mentioned above is yes? As at the moment its ordering sequentially even when some PO's are not approved. I have highlighted this in the attachment.

2. Based on this is it possible to create a macro to automatically populate the data from this excel once it is approved and has a P.O number into an excel P.O template already used in the business also attached?

Thanks
R

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

I have created the PO as you describe it however.....

It will not work in real life.

You are creating the PO from the index sheet.

However the index sheet cannot be completed without information that can only come from the PO.

Eg Qty.... Is that total items in the PO?
Amount Is that the cost of all the Items on the PO?

See what I mean? It might be better to create a new PO using a button and update the Index from that.

I will attach something for you to trial, but I think that we will have to make some changes.


I am attaching what I have as you are online.

10 more minutes and you will have something better.

Ok

This meets your original request.

Take a day to play with it and come back with your amendments.

By the way you should list your suppliers in a table so that you can select the supplier name and have the data auto filled.


Sorry just spotted an error. Reloaded the attachment.

1. The useful code tab. Is for you to learn from. It has nothing to do with this project.
However you will notice that I used

Please Login or Register  to view this content.

in the Solution.

2. Is it possible to get column O to essentially be the number of the last approved PO + 1. So that the PO Number will populate only when they are approved?
That is easily done. I normally store the last PO number in the PO Template. It is a minor thing to Increment that and use it when the PO is approved.

3. On the PO that opens from the MACRO is it possible to protect the box with the PO number in it? (This is not necessary but I might look into it after).
Again it is easy to either protect the cell with a password or with a small macro. The Macro could reinstate the Invoice Number it you change it, or it could make the cell unselectable. we will work on that.

4. One part of the code is essentially locking row 7 down.
I don't think so. I wrote the Macro so you cannot select past row 6 until row 6 is complete.
Try completing row 6 and then select row 7.

I will play with this until you get home. Hopefully we will have moved forward.

Ok.

This Version is much more practical.

Select any cell below the last entry and a new po will be created.

Select the red tab and a new po will be created.

The Index Sheet will be filled with formulas so that it mirrors the data on the PO.

Play with this a bit and then tell me.

1. Where do you want to approve the PO on the PO or on the Index sheet or either?

2. Do you want to select from a list of names or type in the name each time.

3. Do you want a password at the approval stage.

I will be editing this post quite a bit. So Please Check on it.

1. On your PO, B13 and B47 are the same Date.

2. The approvals password is Test

3. I think that you are missing a trick by having the Approval process on the PO itself.
Changing the No to a Yes on the index sheet would be a lot more efficient way of doing that.

Imagine that you have 30 PO's. You have to find the right PO, Scroll to B45 and Select it. Try It.

Play with this version for now. If you agree then I will make the modifications.

4. Another trick that you have missed is with Navigation.
It would be Quite easy to create a macro so that wherever you are in the workbook. Ctrl g would take you back to the index.
Once there select a PO. Press ctrl g to go to that PO. Just a thought.

Anyway.

Have fun.

Hi

A few things to try out.

ctrl q on any sheet to use the navigator.

Select B17 0n any PO to select a Supplier.