Hello,
I hope I am going about the issue I have in the correct way, but if someone sees a simpler way to complete my task I welcome any advice.
I am making an inventory spreadsheet in which I have a weekly inventory that is performed every Monday, in which I want all weeks to be on one page:
allonepage.PNG
I have another sheet, which I will call the calculation page, in which i want to be able to do many functions and calculations automatically that tells me weekly usage, minimum reorder dates, etc. What I need help with is making the calculation page automatically update with the current weeks data. So far to do this I have gotten to the point where I use the "=today()" function in the E1 cell, and the function "=E1-WEEKDAY(E1,2)+1" to get the monday of the current week displayed.
Since inventory is calculated every monday, i then use a index-match function "=CELL("address",INDEX('Weekly Inventory'!B1:U1,MATCH(Sheet1!D1,'Weekly Inventory'!B1:U1,0)))" to identify the cell from the weekly inventory page on the calculation page, i think this is a good step towards the final goal i want of displaying all of the data in the column that comes below that identified cell. For clarification i am including a picture of the calculation page:
calculationpage.PNG
basically, I want the data in B2 on down to automatically populate with a calculation of the difference between the current week and the previous week from the data in the weekly inventory page. I have already manually populated that data to show what the correct numbers would look like, but I feel like I could just add something to my index-match function to plug into those cells and make it happen. Please let me know if any of this is unclear, and I will clarify as best I can.
TIA
-Adam
Bookmarks