Stock Control in Excel

[MarkAdz]

Hi there, I am a newbie on the site. I have a question for the clever people...

Is it possible in Excel to create a stock control sheet pulling the info from an invoice also created in Excel. ie I have the initial stock in excel sheet, when an item is sold, it updates the stock sheet from the invoice?

Hope this makes sense...

Thanks for you assistance!

 

[BenMiller]

Will each invoice be a new sheet? Will they be automatically generated/titled? Is the total amount sold updated manually on a separate sheet as well, or is the only record of items sold kept on your invoices?

 

[MarkAdz]

Hi Ben, thanks for responding. I was more thinking in the line of the invoice being a separate workbook, but I suppose this will make it more difficult. The only record would be of the items sold kept on invoice. It is for a book shop. Client of mine asked me to do this, and before I say yes, I first want to find out it possible. Regards, Nadia

 

[BenMiller]

It's definitely possible. Try posting a sample of how your data will be laid out. An easy way to do that would be with the Excel Jeanie HTML add-in.

 

[MarkAdz]

Sorry, might sound stupid, but what is the Excel Jeanie HTML add-in? Haven't used Excel lately for anything but the basics.

The idea is very basic, something like this:


-- removed inline image ---

 

[BenMiller]

It's just a way to display your sheets on this forum - this was ok enough though

Will the invoices be in the same workbook? Or will they each have their own workbook? Can they have more than one unique item on them?

 

[MarkAdz]

Each invoice will be in own workbook (unless it's not possible to make stock sheet work) and there will be a few unique items...

 

[BenMiller]

Ok, here's am example. You will have to have the original stock number stored in a cell somewhere, because your formulas will be checking for what was sold and deducting it from the original - which you have to have somewhere.

So say your original was in cell B1, with the product code in B2.

Your invoice (we'll start with just one) is in a workbook called "The Other Workbook", on Sheet1, with the product name in column A and the amount sold in column B.

In C1 (on the master sheet), type

=B1-VLOOKUP(A1,'[The Other Workbook.xls]Sheet1'!$A:$B,2,FALSE)

Try that, and we'll work from there.

 

[MarkAdz]

Thank you so much! Okay, now the question is how do we update the stock sheet (master) from different invoices (the other workbook)?

 

[BenMiller]

You mean from more than one invoice? Did you try the step I just showed you?