# Calculating Stock Changes Per Day

#### xcelinexcel

##### New Member
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?

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### srehman

##### Board Regular
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

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

Replies
9
Views
320
Replies
7
Views
167
Replies
1
Views
144
Replies
1
Views
337
Replies
1
Views
634

1,140,999
Messages
5,703,642
Members
421,307
Latest member
morrden86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back