Formula Assistance [Closed]

Report
Posts
1
Registration date
Wednesday April 30, 2014
Status
Member
Last seen
April 30, 2014
-
 Blocked Profile -
Excel.....
I have a spreadsheet for shipping products.
When I enter the amount shipped, I would like for that days date to appear in the next column and not change unless I re enter the quantity shipped. I used a formula but the date updates every day. I want it to show the date shipped.
Any suggestions?

1 reply


OK, I am going to guess that this isn't homework, so I will start you in the right direction, and we will learn some stuff along the way.

The following is modified from code pasted here:
https://ccm.net/faq/1117-excel-vba-detecting-changes-in-cell#q=detecting+change+on+worksheet&cur=1&url=%2F

We can modify it to be really simple like:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Range("$C$1").Value = Now()
End If

In the above example, the shipping cell is address of B1. IF B1 is being acted upon, then the timestamp is written in the next cell over.

What will happen from here, is get that to work, then we will expand to check all of your shipping boxes, figure out which one is being acted on, we will Add 1 to the cell location, and past in the date dynamically based on which row is being acted on.

So let us know when you get the above implemented, then we will grow on it!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!