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]

Click here for your file. Select the item in D25, add the reason in D27 and then insert the quantity in D29 and press the ENTER key. This is the revised code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D13,D25,D29")) 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").MergeArea.ClearContents
            Range("D29").ClearContents
            Range("G29").ClearContents
        Case "D29"
            If Target <> "" Then
                Set item = desWS.Range("A:A").Find(Range("D25").Value, LookIn:=xlValues, lookat:=xlWhole)
                Range("G29") = 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(, 4).Value = Array(item, Target.Value, Now(), Range("D27"))
                End With
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

 

[Dinngz]

Thank you mumps, you've been a great help. Much appreciated

 

[mumps]

You are very welcome. )