Warehouse sold items

[Impulz]

Hi guys,

My bookkeeping is in Excel (and Dutch).
But I want to decrease my stock when I press the button "Factuur opslaan" (CommandClickButton_1)

I also need an increase when stock has been bought, but I can fix that myself if my first question is solved.

I have an example, where "Voorraad" is what I have in my warehouse,
"Factuur invoer" is where you add the sold items to make an invoice
and "Factuur" is where the invoice is been made by "Factuur invoer", this button also saves it also as PDF and an seperate XLS for the customer.

I have found a few solutions, but I can't make it match up with what I want.
voorbeeld.xlsm

[Tinbendr]

This is the way I would do that.

Please Login or Register  to view this content.

[dflak]

I'd take a different approach based on transactions and make use of Excel tables so we can work with exactly the amount of data we have. You probably could also make use of data validation to look up product names.

In addition to the button to make the invoice and decrement the stock, I would also have buttons to do inventory adjustment either for initial supply, restock or based on physical inventory. The transaction table would keep track of all transactions so you know the history. Total stock would be the total of the transactions: Initial Stock - Invoices + Resupply +/- Adjustments. The transaction file can also apply the type of transaction, a date stamp and if needed, what login name made the transaction.

I need some help. If I can get the English translations of the column headers on the Voorraad sheet and tell me what needs to be incremented and decremented, I'll give it a shot.

[Impulz]

This is the way I would do that.

Please Login or Register  to view this content.

Works like a charm, but he doesn't find Olie 5W40

[Impulz]

I'd take a different approach based on transactions and make use of Excel tables so we can work with exactly the amount of data we have. You probably could also make use of data validation to look up product names.

In addition to the button to make the invoice and decrement the stock, I would also have buttons to do inventory adjustment either for initial supply, restock or based on physical inventory. The transaction table would keep track of all transactions so you know the history. Total stock would be the total of the transactions: Initial Stock - Invoices + Resupply +/- Adjustments. The transaction file can also apply the type of transaction, a date stamp and if needed, what login name made the transaction.

I need some help. If I can get the English translations of the column headers on the Voorraad sheet and tell me what needs to be incremented and decremented, I'll give it a shot.

Itemcode | Brand | Itemgroup | Supplier | Location (of the stock) | Stock | Reserved stock | Free stock | Order level | Selling price | Cost Price | Value of Stock | Status

I'll be the only one who makes invoices for now, but for the future, you have great plans..!
I'll be looking forward for your solution to this

[Tinbendr]

Works like a charm, but he doesn't find Olie 5W40

I had this line backwards.

Please Login or Register  to view this content.

[Impulz]

I had this line backwards.

Please Login or Register  to view this content.

Ah..! Thanks..! The row doesnt become red if there already is no stock

Please Login or Register  to view this content.

[Tinbendr]

Hadn't set the range of the red cells.
Also added to change color back to black (automatic color actually) if the order qty was changed to less than/equal qty on hand.

Please Login or Register  to view this content.

[Impulz]

Super..! Thanks..! I get an error on line

Please Login or Register  to view this content.

if I implement it to my live sheet I have an error 13 Type mismatch

I also would like to work this on my incoming stock.. Which I didn't succeed as I mentioned earlier

[Tinbendr]

Run it and when it errors out, hover of each side of the statement to learn which is causing the error. Then investigate it it's pointing to the correct cell.

I also would like to work this on my incoming stock

You indicated you'd take care of this in the opening message, but tell me how it works and I'll look into it.

[Impulz]

Thank you so much Tinbendr..!

'Inkoop factuur' is the invoice I get from the supplier.
I know I am becoming a real pain in the bottom, but it would be marvelous that if the 'Artikelcode' doesn't excist in "Voorraad" sheet, he adds it to the table.

About the error 13 Type mismatch:
These are the results when I hover over the yellow line
NewQty = C.Offset(, 5).Value - .Range("A" & A).Offset(, 5).Value
= 1 =5 =" "

I added an attachment with "Inkoop factuur" sheet

voorbeeld.xlsm

[Tinbendr]

Is the number of items on Inkoop factuur always 11? C6:C16

Do empty fields always state Null? Or is that a result of a formula?

I don't get the error on my machine with the latest worksheet.

[Impulz]

Is the number of items on Inkoop factuur always 11? C6:C16

Do empty fields always state Null? Or is that a result of a formula?

I don't get the error on my machine with the latest worksheet.

It is always 11, I don't think I will get bigger invoices.
Well it is working, it doenst skip anything so no problem

Yes Null is to fill the invoice but I can change that to nothing

[Tinbendr]

It is always 11, I don't think I will get bigger invoices.

Yes Null is to fill the invoice but I can change that to nothing

It's not a problem, just for my clarification.

[Tinbendr]

See how this works. You may have to adjust the column destinations.

Please Login or Register  to view this content.

[Impulz]

Awesome..!
Thanks Tinbendr..!!