Jan 21 2022 07:18 AM
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!
Jan 21 2022 05:34 PM
Jan 22 2022 02:28 AM
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.
Feb 17 2022 07:25 AM
@Sergei Baklan Thank you so much for your help! Truly appreciated!