Excel - Edit Data

Dinngz

New Member
Joined
Jan 28, 2025
Messages
11
Office Version
  1. Prefer Not To Say
Platform
  1. Windows
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
ABCDEFGHIJK
9
10
11Inventory Stock Check
12
13Select Item:
14
15Stock LocationNo. Of Stock
16RACK1, E060
17
18
19
20
21
22
23Book Stock Out
24
25
26
27
28
29
30
31
32
33
Sheet1
Cell Formulas
RangeFormula
D16D16=XLOOKUP(D13, Inventory!$A$2:$A$1234, Inventory!$B$2:B$1234)
G16G16=XLOOKUP(D16, Inventory!$B$2:$B$1234, Inventory!$C$2:C$1234)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G16:H18Cell Value>=5textNO
G16:H18Cell Valuebetween 0 and 2textNO
G16:H18Cell Valuebetween 3 and 4textNO
Cells with Data Validation
CellAllowCriteria
D13:H13List=Inventory!$A$2:$A$1048576


Inventory Page

Stock List.xlsm
ABC
1ItemLocationAmount
2400v 3 Pole Circuit BreakerRACK 1, A0114
3Motor Contact BlockRACK 1, A022
4230v - 415v Single Pole BreakerRACK 1, A035
53 Pole 9 - 14 amp BreakerRACK 1, A041
6Contact AuxillariesRACK 1, A056
7110v Commando Panel Socket - 2P+E (Yellow)RACK 1, A061
8110v Commando Panel Plug - 2P+E (Yellow)RACK 1, A070
916a Commando Socket 3P+E (Red)RACK1, B013
1016a Commando Plug 3P+E (Red)RACK1, B026
1116a Commando Socket 2P+E (Blue)RACK1, B032
1216a Commando Plug 2P+E (Blue)RACK1, B042
1316a Commando Joining Plug Female 3P+ERACK1, B054
Inventory


Much apprecaited
 
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
VBA 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
 
Upvote 0
Solution
Thank you mumps, you've been a great help. Much appreciated :)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top