How to Copy Formulas From Different Worksheets

[danazee]

I am trying to simplify the inventory sheets we are using to make the process easier.  

In an excel file, I will have one sheet for each date we take inventory.  We create the sheet each week that inventory is taken by copying the sheet before it. There is some data I need from the prior week such as beginning inventory and last week's food costs.  I entered the formulas into worksheet #2 and all's good.  However, when I copy that worksheet to create my next inventory sheet (for the following week) it still pulls data from worksheet #1.  I also tried copying and pasting but the same result.  Not sure how to set it up so that it pulls from the sheet before it and not from the first sheet?   The data I need to 'roll-over' each week is highlighted in blue.   I was hoping not to have to change the formulas for each sheet ... because what would be the point?   

I don't see a way to upload the file here, so here is a link.  

Would be appreciative of the help here!  

[#2]

It is better you could use Power Query which will make your work become a mouse click.
In your case you want to copy the data of worksheet2 into worksheet3 but the formular is writen 'worksheet1'?
If it is, you could choose all cells in worksheet3 use replace buttom find worksheet1 (sheet name) and replace to worksheet2 (sheet name).

[#3]

@danazee 

Assuming your periods are 7 days and sheets are named as mmddyy, you may add helper cell

It could be at any place of the worksheet. With that

E3:
=INDIRECT( "'" & $D$4 &  "'!" & CELL("address", D3))+1

B12:
=INDIRECT( "'" & $D$4 &  "'!" & CELL("address", D12))

I12:
=INDIRECT( "'" & $D$4 &  "'!" & CELL("address", G12))

drag latest two down.

After you copy/paste worksheet and enter proper date in D2 formulae take data from previous week sheet.

In genera it's more reliable not to reference cell in another sheet but use any lookup function to pickup value based on inventory code. But that will require to restructure sheets a bit.

[#4]

@Sergei Baklan Thank you so much for your help!  Truly appreciated! 

[#5]

@danazee , you are welcome