Inventory tracking of invoice number

[JPAnderson04]

Hello guys! Im here to ask help for our little business. We are a partner agent of a local logistics company. They provide us shipping forms and ORs (official receipt) to be issued for every transaction. They are Usually in batches.

We issue one shipping form and a receipt number together or sometimes mutiple shipping form but one receipt number only.

A. How to track or make sure that no shipping form and Receipt number will be skipped? What is the good formula for this?

We want to make sure that every form is used and not lost because every shipping form lost has a monetary value. In case we lose it, even the shipping form was not used in any transaction, we will pay for it.

B. From a table of shipping forms (eg. WPS0001 to WPS 0100), i want to automatically remove
a shipping number when mark as USED.


C. How to automatically mark a shipping number as USED/CANCELLED/PAID/UNPAID when used.

Thank you in advanced.

 

[sijpie]

Hi JP, welcome to MrExcel

There are a few ways of handling your issues. But to find the best solution I need to know how you (would like to) work.

I am assuming the shipping form numbers are stored in a worksheet, and
that the Receipt numbers are also stored in a worksheet.
Are they stored in the same sheet, in different sheets? Do new numbers get added to the end? On which cell does this range start? Can the macro delete the entire row?

It is very easy to set up a macro that deletes the row when USED is typed in a cell. But like you say, it is far nicer if this gets automated.

Making things user friendly, takes some planning but is worth it in the end.

The most secure way of doing this is by having the numbers stored in the workbook, where the users can't get to them directly. When they open the workbook, a user form will ask them how many shipping numbers they need, and if a receipt number is required. It then issues these numbers.
The macros then remove the numbers from the available lists and moves them to the used number lists, together with the name of the person that requested the numbers and the date issued.
For completeness there should also be a method to return unused numbers.

So there should be an admin form as well, where the used numbers can be shown and their status can be altered. For the shipping numbers the status can be altered to PAID or CANCELLED. If Cancelled, should they then be returned to the pot?

Let me know your thoughts.

 

[JPAnderson04]

Hi Sijpie ?

thank you so much for reaching out. I was quite hesistant to post in any excel forum for I am a complete beginner on this matter. ?

right now, i placed everything in one worksheet,

the columns look like this.


Date-shipping number-receipt number-type of transaction-amount


you actually guessed what i hoped it would like.
I want a separate list of worksheet where all shipping numbers are encoded.( that i can also add when new batch comes) the shipping number varies BTW. Then if it is used, it is removed from that list then go to another worksheet. If it is cancelled, it will go to another worksheet.

also I hope to include a vlookup where I can see the details related to the shipping number such as transaction date, amount, etc.

any help is highly appreciated. ?

 

[sijpie]

Is it something like this that you want?