Purchase order


Hello, can anyone help me create a  purchase order or tell how to  subtract from my inventory?  

for example : if i order five samll blue tshirt and my inventory  has 11 how do i subtract from the inventory  automatically ?



No problem helping you out but please dont post the same question twice. Please delete one of them.
don (rep: 1960) Mar 25, '17 at 4:56 am
I will answer this one since I assume you will delete the other one like don mentioned to do.
cappymer1 (rep: 120) Mar 25, '17 at 6:39 am
Add to Discussion


Selected Answer


If I read your other questions correctly, which talk about making this spreadsheet database by hand and maintaining it by hand, then you will also have to do this by hand. The only other way is to use a macro to do this. You could create a worksheet that looks like a form with input fields and a button and then just use the macro to subtract the desired amount from the database style worksheet.


I'm sure there are more professional ways to do this but this is an example of how you could do it:

Sub remove_inventory()

'get row
For i = 81 To 149

    If Sheets("UNISEX DATA").Cells(i, 3).Value = (Sheets("UNSEX INVENTORY").Range("A15").Value & Sheets("UNSEX INVENTORY").Range("C15").Value) Then

        rowNum = Sheets("UNISEX DATA").Cells(i, 3).Row

    End If

Next i

'get column
For i = 1 To 8

    If LCase(Sheets("UNISEX DATA").Cells(79, i + 4).Value) = LCase(Sheets("UNSEX INVENTORY").Range("K3").Value) Then

        colNum = Sheets("UNISEX DATA").Cells(79, i + 4).Column

    End If

Next i

orderAmount = InputBox("Order Amount")

Sheets("UNISEX DATA").Cells(rowNum, colNum).Value = Sheets("UNISEX DATA").Cells(rowNum, colNum).Value - orderAmount

End Sub

This only works for entering data into row 15 of the UNSEX tab and this macro is specific to the Small size.

To change the size for which this works, change the cell reference K3 to K4 or whichever size you want.

You can attach this macro to a button for the Small size and run it that way and then copy the macro for each size.

The better way to do it is to create a custom userform for this but involves quite a bit of work to get exactly what you would want. You could do that though and just ask questions on here as you build it to get specific help but a custom form with a custom setup all done for you is something that's generally beyond the scope of forum help.



I dont know any macro i can send u a sample of what i have done
phoneguy (rep: 8) Mar 25, '17 at 10:55 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login