using excel to show an inventory transfer (SOLVED)

[kskinne]

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

 

[kskinne]

some more info. here is the code that i am using in my sheets:

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]

never mind - i found something that works for me

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