View original (French)

Store a value in memory

Solved
phoenix13800 Posted messages 5 Status Member -  
phoenix13800 Posted messages 5 Status Member -
Hello,

I would like to share my issue with you. I am creating an Excel spreadsheet to track product inventory. I have a column for "Reference Stock" (which serves as the basis for my formulas), a column for "Current Stock," a column for "Quantity Out," and a column for "Quantity Received" when I receive my orders.
What I want is for the current stock to change based on the quantity out; for that, I have no problem—I have a formula that deducts the quantity out from the reference stock. However, I also want the quantity received to be added to it, but I need the result to be retained so that with the next order, the value will automatically update.

I hope I have been clear enough; if not, please feel free to ask me questions.

Thank you in advance for your help.

8 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello

    Not clear at all!
    Post an example of your file, with a specific example of the value to remember, on cjoint.com, create a link, copy it and come back to indicate it here

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  2. phoenix13800 Posted messages 5 Status Member
     
    Yes, I'm sorry I tried to be as clear as possible but it’s true that an example is worth more than a long incomprehensible text.

    Here is the link:
    https://www.cjoint.com/c/EIBqAsh83Ye

    What I would like is for the first part of the formula "Actual quantity" (so "Reference stock" - "Quantity received") to be calculated only once and for the value to be stored in memory without changing the formula.
    0
  3. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Re,

    I must be sleepy today, but I don't understand better the that the first part of the formula "Real quantity" (so "Reference stock" - "Quantity received") only occurs once
    Give me a specific example based on the numbers from your table
    --
    "Imagination is more important than knowledge." A. Einstein
    0
  4. phoenix13800 Posted messages 5 Status Member
     
    Okay, it's not easy to explain...

    So, in the table, the actual quantity is based on the base stock (column A), the quantity out (column C), and the quantity received (column B)
    The reference quantity is fixed, the quantity out evolves over time, but the quantity received is filled each time an order is received.
    So if I receive an order of 20, and the next order I get is 50, if I change the 20 to 50, the actual quantity only varies by 30 when it should vary by 50.
    So I would like that even if I erase the 20, the actual quantity does not vary, and therefore I could fill in column B again for each new order received

    (I tried to be clearer, I hope I was...)
    0
  6. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Sorry, I don't really understand much better,

    For my part, I will build my file this way, with one sheet for the database and one sheet for the movements
    http://www.cjoint.com/c/EIBuJQba3Be

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  7. phoenix13800 Posted messages 5 Status Member
     
    Thank you, I'll take a closer look at it tomorrow.

    Thanks for your help anyway.
    0
  8. Raymond PENTIER Posted messages 58218 Registration date   Status Contributor Last intervention   17 485
     
    Hello phoenix13800

    That's not how stock tracking works.
    You need a new line for each operation.

    --
    Retirement is great! Especially in the West Indies... :-)
    Raymond (INSA, AFPA, CF/R)
    0
  9. phoenix13800 Posted messages 5 Status Member
     
    Hello, thank you for your help, and indeed, I think I will lean towards a system where each operation is indicated, as suggested by Raymond PENTIER.

    Thanks again.
    0