Help with making an inventory and production tracker on Excel

Hi Bianca S_222, 

Have a good day !

I value your feedback, please mark it as an answer if it answer your question and click Yes to help me improve the support experience.  

It's so important for me to mark it as an answer. It's help me to have a good motivation forward.

I appreciate your patience with me.    
Looking forward to your response and have a great day ahead!!  
  
Best regards.  

Sting-Ng - Microsoft Community Support Specialist

Hi Bianca S_222, 

Thank you for replying, and the information that you have given to me I really appreciate it.   

I truly appreciate your patience as we look into this matter. Thank you for choosing Microsoft and we value your support. 

Based on your request, it seems you want to enhance the Stock Summary sheet from the previously described Excel inventory/production tracker to include a small summary of the Customer Distribution List and add a column for the Closing Stock specific to each date. Since the Stock Summary sheet is already set up to aggregate data from the Individual Summary and Customer Distribution List sheets, I’ll modify it to include a summary of customer distributions (e.g., total quantity distributed per customer for each flavor on a given date) and ensure the Closing Stock column reflects the stock for each date. I’ll also provide formulas to achieve this, assuming the sheet structures from the previous response. 

We’ll add: 

1. A Customer Distribution Summary section within the Stock Summary sheet (or as a separate table within the same sheet) to show the total Qty distributed per customer for each flavor on a given date.
2. A Closing Stock (Date-Specific) column to confirm the stock level at the end of each date, ensuring it aligns with your calculations.

New Columns: 

Closing Stock (Date): Explicitly shows the closing stock for the specific date, calculated as Opening Stock + Incoming Stock - Wastage - Outgoing Stock. 

Customer Distribution Summary: Concatenates customer names and their distributed quantities for the flavor and date. 

Formulas 

Assuming the columns are laid out as follows in Stock Summary: 

• A: Date
• B: Flavour
• C: Opening Stock
• D: Incoming Stock
• E: Wastage
• F: Outgoing Stock
• G: Closing Stock
• H: Available Stock
• I: Closing Stock (Date)
• J: Customer Distribution Summary

1. Closing Stock (Date) (Column I):  
   1. Formula: =C2 + D2 - E2 - F2
   2. This mirrors the Closing Stock (Column G) but is explicitly labeled for clarity. It calculates the stock after accounting for all transactions on the specific date.
2. Customer Distribution Summary (Column J):  
   1. To summarize the quantities distributed to each customer for a specific flavor and date, we’ll use TEXTJOIN to concatenate customer names and their Qty from the Customer Distribution List sheet.
   2. Formula

=TEXTJOIN(", ", TRUE, IF(('Customer Distribution List'!A:A=A2)*('Customer Distribution List'!C:C=B2), 'Customer Distribution List'!B:B & ": " & 'Customer Distribution List'!D:D, "")) 

How it works:  

• Checks the Customer Distribution List for rows where the Date (A2) and Flavour (B2) match.
• Concatenates Customer: Qty for each matching entry (e.g., "Customer1: 10, Customer2: 10").
• Requires array formula behavior, so press Ctrl + Shift + Enter if using an older version of Excel (not needed in Excel 365/2021).

Alternative (if TEXTJOIN is complex):  

• If you prefer a simpler approach, create a pivot table in the Stock Summary sheet to summarize customer distributions:  
  • Select the Customer Distribution List data.
  • Go to Insert > Pivot Table, place it in the Stock Summary sheet (e.g., starting at cell L2).
  • Set Rows: Date, Flavour, Customer; Values: Sum of Qty.
  • Then reference the pivot table in Column J or use it directly.

Existing Formulas (Recap for Reference):  

• Opening Stock (C): =INDEX('Individual Summary'!B:B, MATCH(B2&MAXIFS('Individual Summary'!F:F, 'Individual Summary'!A:A, B2), 'Individual Summary'!A:A&'Individual Summary'!F:F, 0))
• Incoming Stock (D) and Wastage (E): Pull from Individual Summary similarly.
• Outgoing Stock (F): =SUMIFS('Customer Distribution List'!D:D, 'Customer Distribution List'!C:C, B2, 'Customer Distribution List'!A:A, A2)
• Closing Stock (G): =C2 + D2 - E2 - F2
• Available Stock (H): =C2 + D2 - E2

Notes: 

• Customer Distribution Summary: The TEXTJOIN formula assumes you have Excel 2016 or later. If not, use a pivot table or manually list customers in a separate table.
• Closing Stock (Date): This column is redundant with Closing Stock (G) in this setup but is included for clarity as per your request. If you meant a cumulative closing stock across all dates, let me know, and I can adjust the logic.
• Data Entry: Ensure you enter new rows in Individual Summary and Customer Distribution List to update Stock Summary. The VBA code from the previous response can automate appending rows to Stock Summary.
• Validation: Use data validation for Flavour and Customer to ensure consistency (e.g., Data > Data Validation > List).

Troubleshooting: 

• If the TEXTJOIN formula returns errors, check that Flavour and Date values match exactly between sheets.
• If the summary is too cluttered, consider moving the customer distribution summary to a separate table within the Stock Summary sheet or a new sheet.
• Test with sample data (e.g., 2-3 flavors, 2 customers, 2 dates) to verify formulas.

We look forward to your reply. We will continue to assist you if you any other question or based on the information you provide. I sincerely appreciate your patience and cooperation.   

If my answer is helpful, please mark it as an answer, which will definitely help others in the community who have similar queries to find solutions to their problems faster. 

Thanks and Have a good day!!! 

Best regards.      

Sting-Ng - Microsoft Community Support Specialist

Hi Bianca S_222, 

If my answer helps, please mark it as an answer by click YES, it will definitely help others in the community with similar queries to find a solution to their problem faster. On top of that, it helps me have a good survey and makes me do my best. 

 
 
I appreciate it. Thank you for your support 

Sting-NG - Microsoft Community Support Specialist

Hi Sting,

Thank you for your quick response.

All the information you have provided is so helpful, I appreciate it heaps!

I was wondering if the stock summary could please follow the format that I have put it in in the image I attached - where you also have a small summary of the customer distribution list, as well as another column on the end that shows me the closing stock for that date?

Kind regards,

Bianca :)

Hi Bianca S_222, 

Thank you for replying, and the information that you have given to me I really appreciate it.   

I truly appreciate your patience as we look into this matter. Thank you for choosing Microsoft and we value your support. 

Creating an inventory and production tracker in Excel based on your described setup is absolutely possible! Your idea of using three sheets—Stock Summary, Individual Summary, and Customer Distribution List—with automatic updates and linked calculations is a great foundation. Since you can’t attach the Excel file, I’ll guide you through setting this up step-by-step based on your description and the relationships you’ve outlined. I’ll assume a basic structure for each sheet and provide formulas, tips, and best practices to make it functional and scalable. Let’s build it together! 

You can try step by step setup: 

Set Up the Sheets and Basic Structure 

Create three sheets in a new Excel workbook with the following column headers: 

• Stock Summary:  
  • A: Date
  • B: Flavor
  • C: Opening Stock
  • D: Incoming Stock
  • E: Wastage
  • F: Outgoing Stock
  • G: Closing Stock
  • H: Available Stock
• Individual Summary:  
  • A: Date
  • B: Flavor
  • C: Opening Stock
  • D: Incoming Stock
  • E: Wastage
  • F: Closing Stock
  • G: Log (optional for past entries)
• Customer Distribution List:  
  • A: Date
  • B: Customer ID (#1, #2, #3)
  • C: Flavor
  • D: Qty
  • E: Notes (for record keeping)

Stock Summary (Master Sheet) 

This sheet will automatically log entries from the other sheets. Use a table to manage data dynamically and enable automatic updates. 

• Step:  
  • Select the range (e.g., A1:H100) and press Ctrl + T to create a table. Name it "StockSummaryTable".
  • Leave row 1 as headers and start data entry from row 2.
• Automation:  
  • Since this is a master sheet, you won’t enter data directly. Instead, use a VBA macro or manual copy-paste from the other sheets to log entries. For simplicity, let’s start with manual updates and add a macro later if needed.
  • To log an entry, copy the latest row from Individual Summary (columns A, B, C, D, E, F) and append it to Stock Summary, then adjust the "Outgoing Stock" (F) based on Customer Distribution List.
• Formulas:  
  • In G2 (Closing Stock): =C2+D2-E2-F2 (Opening Stock + Incoming Stock - Wastage - Outgoing Stock)
  • In H2 (Available Stock): =C2+D2-E2 (Opening Stock + Incoming Stock - Wastage)
  • Drag these formulas down as new rows are added.

Individual Summary (Flavor-Level Tracking) 

This sheet tracks each flavor’s stock and maintains a log. 

• Step:  
  • Create a table (e.g., A1:G100) and name it "IndividualSummaryTable".
  • Enter data manually or via a form (we’ll add this later).
• Formulas:  
  • In F2 (Closing Stock): =C2+D2-E2 (Opening Stock + Incoming Stock - Wastage)
  • To link with Stock Summary:  
    • C2 (Opening Stock): Manual entry or reference the previous row’s Closing Stock (e.g., =IF(A2>A1,INDIRECT("IndividualSummaryTable[F" & ROW()-1 & "]"),0) for the first entry).
    • D2 (Incoming Stock) and E2 (Wastage): Manual entry.
  • For the log (G2), use a simple text entry or concatenate key fields: =A2&" | "&B2&" | Closing Stock: "&F2.
• Updating Closing Stock:  
  • Each new entry should calculate a new Closing Stock. Use a running total approach by referencing the previous row’s Closing Stock as the new Opening Stock for the next entry.

Customer Distribution List (Distribution Tracking) 

This sheet records customer distributions and adjusts stock. 

• Step:  
  • Create a table (e.g., A1:E100) and name it "CustomerDistributionTable".
  • Enter data manually (Date, Customer ID, Flavor, Qty, Notes).
• Linking to Individual Summary:  
  • The "Qty" (D2) should subtract from the corresponding flavor’s "Closing Stock" in Individual Summary.
  • Use a VLOOKUP or INDEX/MATCH to find the latest "Closing Stock" for the selected flavor:  
    • In a helper cell (e.g., F2): =MAX(IF(IndividualSummaryTable[Flavor]=C2,IndividualSummaryTable[Date],0)) (array formula, press Ctrl+Shift+Enter).
    • Then, =VLOOKUP(F2,IndividualSummaryTable[[Date]:[Closing Stock]],6,FALSE) to get the latest Closing Stock.
  • However, to update Stock Summary "Outgoing Stock", manually sum the "Qty" per flavor and date, then copy to Stock Summary column F.
• Formulas:  
  • No direct formula to subtract Qty from Individual Summary yet.

Some limitations and workarounds i advise to you: 

• Manual Entry: The current setup requires manual data entry. For automation, consider a user form (VBA) to input data directly.
• Real-Time Updates: The macro runs on demand. For real-time updates, use Excel’s Worksheet_Change event, but this is complex and may slow performance.
• Error Handling: Add checks in the macro to avoid duplicate entries or invalid data

This setup should meet your needs for tracking inventory and production, with room to grow as you get more comfortable with Excel. Great job starting this project—let me know how it goes! 

We look forward to your reply. We will continue to assist you if you any other question or based on the information you provide. I sincerely appreciate your patience and cooperation.   

If my answer is helpful, please mark it as an answer, which will definitely help others in the community who have similar queries to find solutions to their problems faster. 

Thanks and Have a good day!!! 

Best regards.      

Sting-Ng - Microsoft Community Support Specialist.