CODEWITHSUNDEEP
excel
input
input

Reputation: 7519

Calculate between two sheets excel

I'm new to excel and I would like some help on this.

I have two spreadsheets. On the first sheet, I enter job information and in the second sheet, there is a list of total number of stocks corresponding to their work ids.

This is what I want to do:

Sheet 1:
---------------
A1         B1        C1
Ordered  In stock  Work ID
5          95       ABC

Sheet 2:
--------------
A1              B1
Total Stock   Work ID
 100           ABC

In Sheet 1, as the user enters the number of stock ordered [A1], it should subtract from the total stock in Sheet 2 and show the remaining balance in both - cell B1 of Sheet 1 and A1 of Sheet 2.

When the user enters another job of the same work ID, it should subtract from the current total i.e 95 [A1 of Sheet 2] 

Sheet 1:
    ---------------
    A5         B5        C5
    Ordered  In stock  Work ID
    15         80       ABC

    Sheet 2:
    --------------
    A1              B1
    Total Stock   Work ID
     95*           ABC

What would be the best way to do this?

*should become 80.

Upvotes: 1

Views: 11079

Answers (1)

JMax
JMax

Reputation: 26601

What you cannot do with formulas only is giving a first input and getting it changed afterwards.

Thus, you can either use VBA (which you won't like if you are a beginner and don't want to learn too much about it) or try this:

In sheet 2:

Col A          Col B     Col C
Initial stock  Work ID   Current Stock
100            ABC       95* (and then 80)

In sheet 1:

Col A          Col B           Col C
Ordered        In stock        Work ID
5              95* (become 80) ABC
...
15             80              ABC

Here are the formulas you can use:

In sheet 2, column C (cell C2 to start then drag and drop):

=SUMIF(Sheet1!B2:B10, B2, Sheet1!A2:A10)

In sheet 1, column B (cell B2 to start then drag and drop):

=LOOKUP(B2, Sheet2!B2:B22, Sheet2!C2:C22)

Upvotes: 2

Related Questions