going a step beyond index-match function

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

Sadly, the forum is having issues downloading files today. However, in Sheet1, try something like this:

Formula:

Please Login or Register  to view this content.

where B2:U200 is your actual inventory range, the first index finds the matching number for item and week, second INDEX finds matching number from previous week (Same exact formula except it's D1-7).
Questions?

I apologize for my ignorance, but I am a bit of a novice at using these formulas, as evidenced by the fact that I am stuck on this problem. Should I be changing the values for the cells depending on where I place the formula you posted? I currently get an error message when I try to copy and paste that formula.


Maybe I am going about this wrong...If I use a formula to display the contents of a specific cell, but I want to use that cell as a reference and display the contents of a cell below it, is that an easy task to perform?

That formula would go in B2 of sheet1 (based on your post) and you'd copy it down. It looks for the intersection of A2 (the item) and B1 (the week date) - 7 and then subtracts that from the current week number from it.
What sort of error do you get?
Is the inventory sheet name correct in my formula?
Try entering the formula in snippets, for example

Formula:

Please Login or Register  to view this content.

or each of the MATCH functions to see which gives an error. You could also do "Evaluate Formula" on the formula tab to watch Excel step through the calculations.

PS. Someone said that xls files are opening up okay so if you want to save your example file as an xls and then upload that, I will see if I can download it.

Try in B3:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).