inventory example.xlsx I have a sheet that I cannot get to work correctly
My excel sheet is actually an inventory listing for several items including item description, price, # of items on hand, # of items sold, and the remaining total. What I am trying to do it create a field and formula that will take the "remaining total" and put it in it's own column, prior to the "new" remaining total field change. An example is shown below:


PLU # Product Price # On Hand # Sold Remaining Total Previous Total Difference
0001 Soda 1.50 100 59 41
0002 Chips 1.50 100 54 46
0003 Cookies 1.50 100 51 49
0004 Plates 1.50 100 4 96
0005 Cups 1.25 100 14 86

I want to be able to take the Remaining Total #'s and create a formula so that it goes to the Previous Total PRIOR to the # Sold changing at the next inventory. Meaning that right now, as the example shows, I have 41 remaining Sodas. In two weeks, when I do the audit again, I want to be able to open this report and 41 be in the Previous Total field. I will perform the next inventory audit then as well. I now have 64 sold Sodas, which will then take my remaining total to 36. I still want my previous total field to show 41, as it was the last total prior to changing it at this audit. Mind you, the Remaining Total field has a simple formula in it (# On Hand-# Sold).

every time the sheet opens, I would like the data in F to move over to G!

I really really hope this makes sense, because I'm having a hard time finding anything on Google :/ HELP!!

Thanks in advance