Excel - Edit Data

[Dinngz]

Hi All,

Im looking for advice on how i can implement an idea i'm struggling to.

I have an excel workbook where i am storing stock information. This includes Name, Location and Stock Amount, as seen below

Using data validation and then the VLOOKUP function i have pulled in stock information into a form.

I want to essential recreate the form, where the data is pulled in, but instead of number of stock, it will be Book Out Stock, so the user inputs x amount used. This will then edit the data sheet to the now remaining number.

Is anyone able to offer advise on how to achieve this?

Stock List.xlsm
	A	B	C	D	E	F	G	H	I	J	K
9
10
11			Inventory Stock Check
12
13			Select Item:
14
15				Stock Location			No. Of Stock
16				RACK1, E06			0
17
18
19
20
21
22
23			Book Stock Out
24
25
26
27
28
29
30
31
32
33
Sheet1

Cell Formulas
Range	Formula
D16	D16	=XLOOKUP(D13, Inventory!$A$2:$A$1234, Inventory!$B$2:B$1234)
G16	G16	=XLOOKUP(D16, Inventory!$B$2:$B$1234, Inventory!$C$2:C$1234)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G16:H18	Cell Value	>=5	text	NO
G16:H18	Cell Value	between 0 and 2	text	NO
G16:H18	Cell Value	between 3 and 4	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D13:H13	List	=Inventory!$A$2:$A$1048576

Inventory Page

Stock List.xlsm
	A	B	C
1	Item	Location	Amount
2	400v 3 Pole Circuit Breaker	RACK 1, A01	14
3	Motor Contact Block	RACK 1, A02	2
4	230v - 415v Single Pole Breaker	RACK 1, A03	5
5	3 Pole 9 - 14 amp Breaker	RACK 1, A04	1
6	Contact Auxillaries	RACK 1, A05	6
7	110v Commando Panel Socket - 2P+E (Yellow)	RACK 1, A06	1
8	110v Commando Panel Plug - 2P+E (Yellow)	RACK 1, A07	0
9	16a Commando Socket 3P+E (Red)	RACK1, B01	3
10	16a Commando Plug 3P+E (Red)	RACK1, B02	6
11	16a Commando Socket 2P+E (Blue)	RACK1, B03	2
12	16a Commando Plug 2P+E (Blue)	RACK1, B04	2
13	16a Commando Joining Plug Female 3P+E	RACK1, B05	4
Inventory

Much apprecaited

 

[mumps]

Currently when you select a value in D13, the Stock Location and No. Of Stock are populated in D16 and G16 respectively.

instead of number of stock, it will be Book Out Stock, so the user inputs x amount used. This will then edit the data sheet to the now remaining number.

I'm not sure what you mean by this. Do you want to manually enter the x amount used in C23 and subtract that number from the amount in column C of the Inventory sheet? Please clarify in detail referring to specific cells and sheets using a few examples from your data.

 

[Dinngz]

Currently when you select a value in D13, the Stock Location and No. Of Stock are populated in D16 and G16 respectively.

I'm not sure what you mean by this. Do you want to manually enter the x amount used in C23 and subtract that number from the amount in column C of the Inventory sheet? Please clarify in detail referring to specific cells and sheets using a few examples from your data.

I am wanting to create another section below, that allows me to then book stock out. This area would include another drop down box to select the item required from the inventory sheet.

The user would then input (for arguments sake) 2 in cell C24. That would then deduct the value from the row in the inventory sheet that contains the item. So if there was 4 items in stock for x item, it would alter the value by -2 to now show 2 left.

I hope this is better explain

 

[mumps]

Click here to download your file. When you make a selection in D13, cells D16 and G16 will be automatically populated. You don't need the formulas anymore. Next make a selection in D25 and then enter a quantity in D27 and press the ENTER key. Cell G27 will be automatically populated and the amount in the Inventory sheet will be automatically adjusted. The macro runs automatically when make a selection in D13, D25 and enter a value in D27. The code is located in the code module for Sheet1. To view the code, right click the tab name for Sheet1 and click 'View Code'. Close the code window to return to your sheet. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D13,D25,D27")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim item As Range, desWS As Worksheet
    Set desWS = Sheets("Inventory")
    Select Case Target.Address(0, 0)
        Case "D13"
            Set item = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("D16") = item.Offset(, 1)
            Range("G16") = item.Offset(, 2)
        Case "D25"
            Range("D27").ClearContents
            Range("G27").ClearContents
        Case "D27"
            Set item = desWS.Range("A:A").Find(Range("D25").Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("G27") = item.Offset(, 2) - Target.Value
            item.Offset(, 2) = item.Offset(, 2) - Target.Value
    End Select
    Application.ScreenUpdating = True
End Sub

 

[Dinngz]

Click here to download your file. When you make a selection in D13, cells D16 and G16 will be automatically populated. You don't need the formulas anymore. Next make a selection in D25 and then enter a quantity in D27 and press the ENTER key. Cell G27 will be automatically populated and the amount in the Inventory sheet will be automatically adjusted. The macro runs automatically when make a selection in D13, D25 and enter a value in D27. The code is located in the code module for Sheet1. To view the code, right click the tab name for Sheet1 and click 'View Code'. Close the code window to return to your sheet. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D13,D25,D27")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim item As Range, desWS As Worksheet
    Set desWS = Sheets("Inventory")
    Select Case Target.Address(0, 0)
        Case "D13"
            Set item = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("D16") = item.Offset(, 1)
            Range("G16") = item.Offset(, 2)
        Case "D25"
            Range("D27").ClearContents
            Range("G27").ClearContents
        Case "D27"
            Set item = desWS.Range("A:A").Find(Range("D25").Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("G27") = item.Offset(, 2) - Target.Value
            item.Offset(, 2) = item.Offset(, 2) - Target.Value
    End Select
    Application.ScreenUpdating = True
End Sub

Hats off to you

Very much appreciated and exactly what I was after. Thank you ever so much

 

[Dinngz]

Click here to download your file. When you make a selection in D13, cells D16 and G16 will be automatically populated. You don't need the formulas anymore. Next make a selection in D25 and then enter a quantity in D27 and press the ENTER key. Cell G27 will be automatically populated and the amount in the Inventory sheet will be automatically adjusted. The macro runs automatically when make a selection in D13, D25 and enter a value in D27. The code is located in the code module for Sheet1. To view the code, right click the tab name for Sheet1 and click 'View Code'. Close the code window to return to your sheet. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D13,D25,D27")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim item As Range, desWS As Worksheet
    Set desWS = Sheets("Inventory")
    Select Case Target.Address(0, 0)
        Case "D13"
            Set item = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("D16") = item.Offset(, 1)
            Range("G16") = item.Offset(, 2)
        Case "D25"
            Range("D27").ClearContents
            Range("G27").ClearContents
        Case "D27"
            Set item = desWS.Range("A:A").Find(Range("D25").Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("G27") = item.Offset(, 2) - Target.Value
            item.Offset(, 2) = item.Offset(, 2) - Target.Value
    End Select
    Application.ScreenUpdating = True
End Sub

Just on top of this... I've had an extra idea. On booking out the item, would it be possible to save what part has been booked out with number and a date time stamp to a separate page for historical records?

 

[mumps]

Create another sheet and name it "Historical". In that sheet enter the headings "Item" in A1, "Quantity" in B1 and "Date" in C1. Replace the current macro with this one:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D13,D25,D27")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim item As Range, desWS As Worksheet
    Set desWS = Sheets("Inventory")
    Select Case Target.Address(0, 0)
        Case "D13"
            Set item = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("D16") = item.Offset(, 1)
            Range("G16") = item.Offset(, 2)
        Case "D25"
            Range("D27").ClearContents
            Range("G27").ClearContents
        Case "D27"
            Set item = desWS.Range("A:A").Find(Range("D25").Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("G27") = item.Offset(, 2) - Target.Value
            item.Offset(, 2) = item.Offset(, 2) - Target.Value
            With Sheets("Historical")
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Array(item, Target.Value, Now())
            End With
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Dinngz]

Create another sheet and name it "Historical". In that sheet enter the headings "Item" in A1, "Quantity" in B1 and "Date" in C1. Replace the current macro with this one:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D13,D25,D27")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim item As Range, desWS As Worksheet
    Set desWS = Sheets("Inventory")
    Select Case Target.Address(0, 0)
        Case "D13"
            Set item = desWS.Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("D16") = item.Offset(, 1)
            Range("G16") = item.Offset(, 2)
        Case "D25"
            Range("D27").ClearContents
            Range("G27").ClearContents
        Case "D27"
            Set item = desWS.Range("A:A").Find(Range("D25").Value, LookIn:=xlValues, lookat:=xlWhole)
            Range("G27") = item.Offset(, 2) - Target.Value
            item.Offset(, 2) = item.Offset(, 2) - Target.Value
            With Sheets("Historical")
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, 3).Value = Array(item, Target.Value, Now())
            End With
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Excellent, Much appreciated mumps

If i wanted to add more cells at a later date that would pull to the historical sheet, how would i input this code? Would i copy the case "D27" and adjust for the cell i want?

 

[mumps]

You are very welcome.
You would add the code to case "D27". The code would depend on which data you want to add and the location of its destination. Also, when replying to a post, use the "Reply" button rather than the "+Quote" button to keep things simple unless there is a specific reason to add the quote.

 

[Dinngz]

Im planning to add a simple text box, so it would pull the text typed and insert it into the historical data with the part booked out. It would essentially be a description to say why it was booked out