change date cell automatically if number in inventory is decreasing

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: change date cell automatically if number in inventory is decreasing

  1. #1
    New Member
    Join Date
    Jun 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question change date cell automatically if number in inventory is decreasing

    Im new with excel and i was wondering if there is a way to make the date change automatically for today's date if i decreased the number of item in my inventory.

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    Sure that is possible but we need a lot more details before we can provide some help.

    How are the inventory numbers decreasing. By a formula change or manually by changing a range value.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jun 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    Inventory List 2017-06-19

    Inventory ID color "Quantity in Stock" Date de mise en inventaire Quantity of days in stock

    *HP 107 Gloss black 7 2017-04-04 76 Days
    *HP 108 Gloss black 5 2017-04-04 76 Days

    This is the basis of what i did. The worker are going to change the number of (quantity in stock) manually.
    I put a formulla in the ( quantity of days in stock ) (=INT((TODAY()-E3)) & " Days" ) so i can know automatically how much has passed since last time we used some.

  4. #4
    New Member
    Join Date
    Jun 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing


  5. #5
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    I would like to help you but I said in my previous post I needed a lot more details.

    Your original post said:
    if there is a way to make the date change automatically for today's date if i decreased the number of item in my inventory.


    Two major questions:

    What cell is going to be changed Manually?
    And in what cell do you want todays date entered into?

    Do you not agree this is something we would need to know. And do you agree you have not given those details. And I never click on links. So posting links here to show something does not help me.

    And are you willing to use a Vba solution?
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  6. #6
    New Member
    Join Date
    Jun 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    the cell that is going to change manually is (d3) (quantity in stock) and the cell that need to get todays date will be (e3)(date de mise en inventaire)

    And sure if it can help using vba solution i will learn about it and start using it.

  7. #7
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    Try this:

    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D3")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Range("E3").Value = Date
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    The above script assumes you will only be changing the value in "D3" like you said. If you plan to change the values in other rows in column "D" and want a date entered in column "E" then I would need to modify the script.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

  9. #9
    New Member
    Join Date
    Jun 2017
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    i check how vba work and i put th code like you told me so its working for "D3". But yes i would like to do for all of the other row in column "D" and want a date entered in column "E".
    And sorry if i was late to respond it is my first time playing with vba so i was checking some thing here and there.

  10. #10
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    10,657
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default Re: change date cell automatically if number in inventory is decreasing

    Try this:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D:D")) Is Nothing Then
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    Target.Offset(, 1) = Date
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please I知 not perfect yet. "Memories are forever"

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com