#### xcelinexcel

##### New Member

- Joined
- Apr 28, 2021

- Messages
- 2

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:

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?