Calculating Stock Changes Per Day

xcelinexcel

New Member
Joined
Apr 28, 2021
Messages
2
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
Joined
Jan 4, 2020
Messages
190
Office Version
  1. 2016
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top