So, I have got another question from the same viewer from my previous video of linking CELLs
between different SPREADSHEETS.
This time the problem is a bit more complicated!
You can look at this problem in your own time.
In the previous video, I have shown you how to link your SHEET1, some of all these CELLs
on B1 to B30 into SHEET2 B1 CELL.
So, this is the SUM of all these stocks, but now the user is saying, I want just to add
all my stocks onto CELL B1, and it would override CELL B1, obviously, but it would remember
the previous value in here, and add the B1 value to the previous value in here, and that
would become the current stock.
So, let's do this!
It's a bit more complicated and we have to use VBA code to be able to do this.
But, don't worry I will show you step by step how to achieve this!
First of all, we need to reset this value of current stock to 0.
Then, we go to SHEET1, and then we just get rid of the other CELLs because we just want
to use this value to add.
Then, go to the DEVELOPER TAB, and then go to Visual Basic.
In here, just make sure you double click on SHEET1, and we need to create our first sub.
So, we type “Private Sub worksheet_ change (byval target as range)”, ENTER, OK.
You can make this capital.
Now, we need to define a range.
So, “dim keycells as range”, ENTER, “Set keycells = range” and we want our range
to be (“B1”), ENTER.
So, we are defining a range by which when that value changes, we're going to activate
another sub to do the calculations.
So, to make sure that not any CELL you change on your SHEET1, would trigger this function
or sub, we need to create an if statement.
So, “if not application.intersect (keycells, range( target.address)) is nothing”, then
ENTER.
Basically, this means if B1 has changed, then you do whatever we want to do inside this
if statement, otherwise, we wouldn't do anything, we would just ignore it so, but what we want
to do first?!
We need to define a temporary integer, and then we need to give that integer our value,
which is “temp = range (“B1”).value”.
So, this temp would take the value of CELL B1 from SHEET1, ENTER.
So, so far so good, but we need to now create a MODULE.
So, go here right click, and then click on INSERT, and click on “Insert a module”,
and we need to make our MODULE public, so other SHEETs could access it, and we call
it “AddStock1” because we want to keep adding the new stock to the current stock.
Again, we will go by reference and we need to Define a variable, which we call is “Stock1Qty
as integer”, because the quantity not going to be a decimal, it's going to be an integer!
Then, we need to Define another temporary integer, and then we need to give that temporary
integer a value.
So, I'm going to give WORKSHEETs.
So, what we are doing here is, we are going to SHEET2, and we are accessing the value
of CELL P1, and we are assigning that to our temporary variable which is defined as integer,
and then what we basically want to do is to add WORKSHEET.
We want to access the same CELL, but this time we want to add the temp.
So, temp is the current value which was 0, with the one that the SHEET1 gonna send to
us, and we call that “stock1qty”.
Then, you go to the next line, and we have now finished the coding.
So, this is the code on SHEET1, and this is the code on Module1.
Now, that we have finished this Public Sub, we need to go to SHEET1, and we need to call
this.
Simply, type “addstock1” and then it will recognize it, and then we just want to give
it the temporary value, and here don't forget to close the if statement, by typing “End
If”.
You can save.
So, remember current stock is 0, and here if I now type 20, this becomes 20.
So far so good, and that formula is not there anymore, and we want now to add more stocks
to stock1, we add 10.
This value is now overwritten, and then if I go there, it became 30.
So, you can see 10 + 20 = 30.
And, let's put 7 to make it around 100 in here, and we got 100, just a revision here,
this line intercepts CELL B1.
Then, we give the value of CELL B1 to an integer, and then we send it to this Public Sub, which
is saved in our module, and that Public Sub would add the two values together, and then
we get the current stock!