Solve inventory table problems: several problems encountered and function solutions

Excel table problems: Several problems encountered when using functions to make inventory tables

Because of this problem, the inventory table I set up is the same table for "in, out, and deposit".

As far as feeding is concerned, the number of feedings cannot be determined, and the discharging is even more uncertain. Simply filling out the inventory form with "=" will leave a lot of spaces.

You can use array formulas to return the numerical value of the product name (number, specification, etc.) that meets the conditions. However, it should be noted that the array formula must have a specific range, and there cannot be null values ​​in the range. When the number of rows cannot be determined, the formula cannot be referenced.

Answered by: Stcxj - Second Place Level 13 2009-8-25 15:58

Just add:

It is because it is split into three tables that the product names in the "save" table cannot be automatically returned. So I said, I set up the same form for "in, out, and deposit". The key is the setting in the "Save" column (column):

Column A is the product name (search condition), column B is for entering, column C is for listing, and column D is for saving.

Enter from the last row, column D (assuming it is the 100th row), that is, D100

=IF(AND(A100"＂",COUNTIF(A100:A$100,A100)=1),SUMIF(A:A,A100,B:B)-SUMIF(A:A,A100,C :C), "") Enter and fill upward!

It may be difficult to understand what you are saying here. Hi or leave your address and I will send a copy to you.

"zopey" talks about using the "VLOOKUP" function. You may not have been exposed to the "in and out" problem, right? On the same day, many "pens" of goods with the same product name and specifications can be "in and out", and only the top data can be returned.

What functions can be used in excel tables to realize inventory changes with daily entry and exit

Assume that the table has only 30 columns. The categories of inventory, outbound, and inbound quantities are inventory, outbound, and inbound respectively

Click Development Tools-VB Editor or press ALT F11-double-click sheet1-copy the following code to the window

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.End(xlUp) "Outbound" And Target.End(xlUp) "Inbound" Then Exit Sub 'Do not run this program if you change the non-outbound or inventory column

If Target.Cells.Count > 1 Then Exit Sub 'If the changed cell is greater than 1, do not run the program

If Not Application.WorksheetFunction.IsNumber(Target) Then Exit Sub 'If the changed cell is not a numerical value, do not run the program

Dim rng As Range, k%

For Each rng In Range(＂A1:AD1＂)

If rng = "Inventory" Then

k = rng.Column

Exit For

End If

Next

Application.EnableEvents = False

If Target.End(xlUp) = "Outbound" Then 'When changing outbound

Cells(Target.Row, k) = Cells(Target.Row, k) - Target 'Inventory=original inventory-output quantity

Else 'When changes are entered into the database

Cells(Target.Row, k) = Cells(Target.Row, k) Target 'Inventory=original inventory-output quantity

End If

Application.EnableEvents = True

End Sub

The above is the detailed content of Solve inventory table problems: several problems encountered and function solutions. For more information, please follow other related articles on the PHP Chinese website!