Inventory - Marking stock that has shipped as shipped.

**CJ2308** · 07-30-2015, 12:53 PM

Hopefully someone can help me with this.

I have a Master spreadsheet with all my inventory that I track by Serial Number.

I have my Invoices linked to the master inventory spreadsheet so I just enter the serial number and it populates the necessary info on the Invoice.

What I want to do is somehow Mark the serial number as shipped/sold on the master inventory spreadsheet when I use it on the invoice.

Not sure if there is a formula or if I will need a Macro. I am a definite beginner with Excel so any help would be appreciated with this issue.

**JohnTopley** · 07-30-2015, 01:23 PM

Could you please post a sample file showing expected outcomes.

Click "Go advanced" then Paper Clip icon to upload a file.

**CJ2308** · 07-30-2015, 02:31 PM

So here is a simple example of what I am trying to do.

Two files. Invoice and Inventorym

Keep in mind, the Invoice worksheet is a template, once I complete the invoice, I am going to save it under a different file name, the file name will be the invoice #.

I have over a 1000 serial Numbers in there, so what I need to happen is this.

1. I type serial number onto the invoice using a blank invoice template and the invoice pulls all the pertinent information from the inventorym sheet. (I have this part figured out with formulas)
2. Ideally I would like the cell on the inventorym sheet to show the invoice# that the serial number is going on.
3. I will then save the Invoice with a file name that matches the invoice# used.

What would I put in that cell under the inventorym worksheet that would recognize the serial number after I save the invoice spreadsheet under a new name?

Thanks for the help.

**JohnTopley** · 07-30-2015, 03:05 PM

I believe you would need VBA to do this so that when a new invoice is created the VBA is invoked (by the Invoice template) to add details to your Inventory file i.e. serial number(s) and corresponding Invoice number. It cannot be done by formula.