Help with coding.

[ragananthony7911]

I am wanting to make a spreadsheet to track inventory levels.
For simplicity say I have two items
each item has 5 parts.

i want to be able to add quantity to say a certain cell for inventory for Kit A (which would automatically deduct the necessary amount of parts needed) and keep those inventory levels accurate as well.

Example.
Kit A
Part 1 6
Part 2 12
Part 3 6
Part 4 6
Part 5 12

Say each kit A takes 1 Part 1, 2 Part 2, 1 Part 3, 1 Part 4, and 2 Part 5.

Each day I would like to keep track of how many Kits we make say we made 5.
If I input 5 in that days cell, I would like it to automatically deduct the parts needed for 5 kits from the "current" inventory level of parts.

Is that possisble?

 

[dreid1011]

Yes, that can be done. If you can provide a sample of how you expect it to look, I'm sure someone can work something up for you.

 

[ragananthony7911]

I would like to be able to enter through next to each seats and back to either add or takeaway inventory. So under Production Log next to seats if I enter 5 it would take away from the corresponding totals in the on hand.
And add under the component punch log.

 

[ragananthony7911]

does that make sense?

 

[dreid1011]

So subtract from on-hand if you enter amounts in Production Log, but add to on-hand if you enter amounts in Punching Log?

Where is the breakdown of how many components a part requires?

 

[ragananthony7911]

So subtract from on-hand if you enter amounts in Production Log, but add to on-hand if you enter amounts in Punching Log?

Where is the breakdown of how many components a part requires?

I attached the revision plus added the row numbers.
What I need to happen:

Amounts added in row 6 and 7 (cell F and beyond) to add to cells C6 and C7 respectively.

Additionally for each value entered in row 6 (Seats) i need it to remove from the components
2 ST
2 SSB
1 FSB
2 Seat Zipper
1 Pull Band

so those values multiplied by how many seats are produced. 3 Seats would take 6 ST, 6 SSB, 3 FSB, 6 Seat Zipper, and 3 Pull Band

For each value entered in row 7 (Backs) i need it to remove from the components:
2 Back SD
1 Back IB
1 Bottom Panel

The shipped Log
any values entered in row 30 and 31 would need to remove on hand from cell C6 and C7 respectively.

and then similar to production. The Component Punch log:
Any values entered into rows 15-19 and 21-23 would need to add to the running total in C15-19 and C21-23 respectively.

 

[dreid1011]

Okay, I believe I understood what you're after:

Book1 10-1-2024.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Bean Bag Production	Updated: 9/30/2024 midday
2
3
4						Production Log
5		Name	QTY		Date	9/29/2024	9/30/2024	10/1/2024
6		Seats	62		Seats	5			g	Adds to C6 and removes 2 ST / 2 SSB / 1 FSB / 2 Seat Zipper / 1 Pull Band from Components On-Hand QTY
7		Backs	66		Backs	3			g	Adds to C7 and removes 2 Back SD / 1 Back IB / 1 Bottom Panel from Components On-Hand QTY
8										*removes above quantities per amounts entered
9
10		Components On-Hand
11						Component Punching Log
12		Name	QTY		Date
13		Zipper	267
14
15		Seat ST	130		Seat ST	20			g	Adds to C15
16		Seat SSB	160		Seat SSB	25	25		g	Adds to C16
17		Seat FSB	55		Seat FSB				g	Adds to C17
18		Seat Zipper	110		Seat Zipper				g	Adds to C18
19		Pull Band	80		Pull Band				g	Adds to C19
20
21		Back SD	112		Back SD				g	Adds to C21
22		Back IB	56		Back IB				g	Adds to C22
23		Bottom Panel	56		Bottom Panel				g	Adds to C23
24
25
26		Possible Bags				Shipped Log
27		Bags On-Hand + bags if components are sewn
28
29		Name	QTY			shipped	12
30		Seats	121		Seats	9			g	Removes from C6
31		Backs	123		Backs	10	-9		g	Removes from C7
Sheet1

The "g"'s in column I are arrows in the Wingdings 3 font and just for my reference.

The only note I might add is if you enter a value either in error or decide it was a wrong value, you will either have to re-enter that same value as a negative or manually adjust your column 6 values to reset the QTY's to before the wrong value was entered. I think re-entering a negative value and then deleting that cell value would be the most efficient. Or don't delete if you want to keep it for tracking purposes. I also set the columns to 100 for now, not knowing your max range. You can change the "DA" portions in the Union to add/remove columns.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputRng As Range

'Set range for allowed input columns. Currently set for 100 columns. Adjust "DA" reference as needed:
Set inputRng = Union(Range("F6:DA7"), Range("F15:DA19"), Range("F21:DA23"), Range("F30:DA31"))

On Error GoTo ExitNow
Application.EnableEvents = False

If Not Intersect(Target, inputRng) Is Nothing And Target.CountLarge = 1 Then
    Select Case Target.Row
        Case 6, 7
            Range("C" & Target.Row) = Range("C" & Target.Row) + Target
            If Target.Row = 6 Then
                'Subtract components from On-Hand for each seat produced. Adjust multiplier as needed:
                Range("C15") = Range("C15") - (2 * Target)
                Range("C16") = Range("C16") - (2 * Target)
                Range("C17") = Range("C17") - (1 * Target)
                Range("C18") = Range("C18") - (2 * Target)
                Range("C19") = Range("C19") - (1 * Target)
            Else
                'Subtract components from On-Hand for each back produced. Adjust multiplier as needed:
                Range("C21") = Range("C21") - (2 * Target)
                Range("C22") = Range("C22") - (1 * Target)
                Range("C23") = Range("C23") - (1 * Target)
            End If
        Case 15, 16, 17, 18, 19, 21, 22, 23
            Range("C" & Target.Row) = Range("C" & Target.Row) + Target
        Case 30
            Range("C6") = Range("C6") - Target
        Case 31
            Range("C7") = Range("C7") - Target
        Case Else
            GoTo ExitNow
    End Select
End If

ExitNow:
Application.EnableEvents = True

End Sub