Add and subtract from another sheet

[Jasmin74]

Hello,
I have a workbook with two sheets
Sheet 1- Oldstock 2021-2022 : It contains warehouse information

Sheet 2- Transaction
we are selling & returning items stated
Question 1:
About the information we got from the first article
We have two processes: the sale (Sale) and the return (retrieval) and the quantity sold or returned
After putting the quantity sold, I want to make a confirmation in order to transfer NEWSTOCK to Quantity in stock on the first sheet.


Question 2:
2- How to add the current date automatically each time I add a new line
I put it now () but how is it written automatically

The file is attached.

test-0-fr-m.xlsm

drive.google.com

Thank you.

 

[maabadi]

Welcome to MrExcel Message Board.
Please update account details to we know what version of excel & Platform you used and guide better based them.

After putting the quantity sold, I want to make a confirmation in order to transfer NEWSTOCK to Quantity in stock on the first sheet.

After add formula at first sheet, your previous quantity at Transaction Sheet affected, because of related formula.
But try this:
At cell E3 in first Sheet input this (update range to your last row also)

=LOOKUP(2,1/((Transaction!$B$3:$B$13='OldStock2021-2022'!C3)*(Transaction!$G$3:$G$13>0)),Transaction!$I$3:$I$13)

For Now function:
first go to File Tab, excel option, formula section and Enable iterative calculation (add tick mark)
then at Cell A3 at 2nd sheet Write:

=IF(B3<>"",IF(A3="",NOW(),A3),"")

and drag it down.
Now whenever you input new data at column B at the same row, Date and Time appears.

 

[Jasmin74]

Thank you sir, as i am a newbie can you do the appropriate to the file & send it to me so that I can explore it?
I appreciate your help.

 

[maabadi]

Please update account details to we know what version of excel & Platform you used and guide better based them.

What is difference of Column K & E Values at first sheet?

 

[Jasmin74]

K must not to be changed - because I can't protect the sheet if I want to change values-
While E is the one that must be changed with the relative newstock from the Transaction sheet

 

[maabadi]

Then We can Use Column K values at your first original Stocks value at another formulas at source.

AND Again

Please update account details to we know what version of excel & Platform you used and guide better based them.

and don't forgot to save.

 

[Jasmin74]

E is the moving balance, going down on sale & up on retrieval from sheet transaction.
Thank you.

 

[maabadi]

Try this at Column E of First Sheet :
Start from E2 and drag it down:

=IF(COUNTIFS(C2,Transaction!$B$3:$B$13)=0,VLOOKUP(C2,'OldStock2021-2022'!$C$2:$K$7,9,FALSE),LOOKUP(2,1/((Transaction!$B$3:$B$13='OldStock2021-2022'!C2)*(Transaction!$G$3:$G$13>0)),Transaction!$I$3:$I$13))

And Column E at 2nd sheet start from E3 and Drag it down:

=IF(COUNTIFS($B$3:B3,B3)=1,VLOOKUP(B3,'OldStock2021-2022'!$C$2:$K$7,9,FALSE),LOOKUP(2,1/((Transaction!$B2:$B$3=B3)*(Transaction!$G2:$G$3>0)),Transaction!$I2:$I$3))

 

[Jasmin74]

Nope.. That's not it..

 

[maabadi]

What's the problem. Describe it with Details.