using excel to show an inventory transfer (SOLVED)

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
I have a workbook that contains inventory information. in Sheet1 there is a list of the inventory for a particular inventory location, arranged as follows:

column A - inventory item name
column B - beginning quantity
column C - transfers in
column D - sales
column E - transfers out
column F - ending balance (=B+C-D-E)

Sheet2 represents another inventory location, and the layout of data is identical.

What I need to do is figure out a method so that, if I enter a number in the 'transfers in' column for a particular item of inventory on one of the sheets, the same number will automatically be entered in the 'transfers out' column for the same inventory item on the other sheet.

Anyone have any suggestions?

tia-
kevin
This message was edited by kskinne on 2002-10-22 14:43
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
some more info. here is the code that i am using in my sheets:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address <> "$A" Then Exit Sub
If Target.Value = 0 Then Exit Sub
Select Case [A2].Value
    Case "Begin"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value + 1
        End If
        Range("A1").Select
    Case "Tr.in"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, 4).Value + 1
        End If
        Range("A1").Select
    Case "Sale"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, 5).Value + 1
        End If
        Range("A1").Select
    Case "Tr.out"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(0, 6).Value + 1
        End If
        Range("A1").Select
    End Select
End Sub

i have two sheets representing separate inventory locations. basically i just need to add something to this code so that in addition to adding another quantity to the transfer in column, it will add another quantity to the transfer out column in the other sheet. anyone have any ideas?

thanks,
kevin
 

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
never mind - i found something that works for me

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address <> "$A" Then Exit Sub
If Target.Value = 0 Then Exit Sub
Select Case [A2].Value
    Case "Begin"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value + 1
        End If
        Range("A1").Select
    Case "Tr.in"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 4).Value = ActiveCell.Offset(0, 4).Value + 1
            If [A3].Value = "" Then GoTo 1
            myrange = ActiveCell.Offset(0, 6).Address
            Sheets(Range("A3").Value).Range(myrange).Value = Sheets(Range("A3").Value).Range(myrange).Value + 1
        End If
1
        Range("A1").Select
    Case "Sale"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 5).Value = ActiveCell.Offset(0, 5).Value + 1
        End If
        Range("A1").Select
    Case "Tr.out"
        Cells.Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
        If ActiveCell.Address = Target.Address Then
            MsgBox ("No Matching UPC Found")
            Else
            ActiveCell.Offset(0, 6).Value = ActiveCell.Offset(0, 6).Value + 1
            If [A3].Value = "" Then GoTo 2
            myrange = ActiveCell.Offset(0, 4).Address
            Sheets(Range("A3").Value).Range(myrange).Value = Sheets(Range("A3").Value).Range(myrange).Value + 1
        End If
2
        Range("A1").Select
    End Select
End Sub

thanks
kevin
 

Forum statistics

Threads
1,144,328
Messages
5,723,727
Members
422,512
Latest member
MHau5

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
Top