Calculating Stock Changes Per Day

[xcelinexcel]

I'm working on a stock list and would like to calculate the stock difference day by day.

I'm facing these issues.

Scenario 1:

Now every day I need to copy and paste the formula for the difference of the total stock from 2 different sheets.

Here's the simple formula after the total stock is totaled.

=Sheet_2!C1-Sheet_1!C1

Now when I create sheet 3, I need to copy the formula from sheet 2 and copy it on sheet 3. Now the formula I copied won't be automatically updated, it will be

=Sheet_2!C1-Sheet_1!C1

and I need to udpate it to

=Sheet_3!C1-Sheet_2!C1

Is there a formula that will automatically update it to the new sheet names?

E.g., when I create Sheet 3 then, it'll be

=Sheet_3!C1-Sheet_2!C1

E.g., when I create Sheet 4 then, it'll be

=Sheet_4!C1-Sheet_3!C1


Scenario 2:

The stock units list changes every day depending on the sales.

E.g.,

Sheet 1 (Day 1) - Sorted by descending order of stock

Oranges 30 units remaining
Apples 15 units remaining
Pears 12 units remaining

Sheet 2 (Day 2)

Oranges 30 units remaining
Pears 11 units remaining
Apples 7 units remaining


Sheet 3 (Day 3)

Apples 7 units remaining
Pears 6 units remaining
Oranges 5 units remaining

This is a table with many different columns with different data (e.g., item name, SKU, stock, stock value, overstock, etc. 19 different fields).

What is the formula for change in stock when the items keep moving up and down the table given different sales volumes, also due to errors in the data source sometimes the items disappear completely so a direct formula wouldn't work?


This is what I did before but it wasn't practical given the issues above.

1. On Sheet 3, I created a cell that got the stock from the previous day with the formula =VLOOKUP(Day_3!A3,Day_2!3:3,3,0)
2. I used this formula to calculate the change in stock =(C3-T3)/C3

This doesn't really work as the items on the list change order every day depending on the sales.

What formula can I use to solve this issue?

 

[srehman]

create table , use power query ( perform maths operation as per your scenario) would be very simple and easy, every time you just need to dump a data and refresh

 

[xcelinexcel]

Ok, I'm not familiar with Power Query. will need to look into that.