Forum Discussion

Katie_Streeter's avatar
Katie_Streeter
Copper Contributor
Oct 18, 2024

Master List across multiple tabs

Within one workbook, I have a products list with unique IDs. Each month new inventory comes in and at the end of the month inventory is counted. There is a tab for each month.
COL A- product unique ID

COL B- product name 

COL C- starting count (same as end of month count, COL F from previous month) 

COL D- incoming count

COL E- formula of amount distributed 

COL F- end of month count 


For simplicity let’s say in January there are 3 products. Rows: 

ROW 1- headers

ROW 2- product 1

ROW 3 -Product 2

ROW 4- Product 3

ROW 5- TOTALS

 

PROBLEM: every month we get 1 or 2 new products (adding a row or 2). I am trying to figure out how to maintain a master products list of the product and unique IDs that will update across all months. This would need to push the total row down (I’m not opposed to moving total to the top) AND keep the formulas in COL C and E (COL D and F are manual input). 

is this possible? 

EDIT OCT 20: For clarity (asked in a PM)…Formulas used in COL C and COL E are very simple:
COL C= COL F of previous month
COL E(for say ROW 2)= 2C+2D-2F

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    Katie_Streeter 

    Here is an attempt with VBA. Simple example in the attached file.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources