EXCEL - inserting fields depending on date creation

[Diezz]

Hello,

I have 2 sheets, one named "Case", and one "Relance".

Each case has a date of creation (witch i would like to be inserted automaticaly when i write the case number) and in the second sheet, i want at a certain date (for example after 30 days of the creation of the case), for the name of the case to copy itsefl in the sheet "relance" so i would know witch case to relance.

For the sheet "Case" , the date inserts itself when i put the number of the case, however it changes when i create a new case for all the cases, not just for a particular one.

Also i can't get the formula or vba code for the copy of the case name in the second sheet at a certain date.

Anyone has some ideeas?

 

[GlennUK]

For the sheet "Case" , the date inserts itself when i put the number of the case, however it changes when i create a new case for all the cases, not just for a particular one.

... you are asking us to fix this but haven't said how this is being done. Well, what method is being used here?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Diezz]

Hello,

In the sheet case this is what i have:
a2=case number (number, integer)
b2=IF(A2<>"",DAY(NOW()),"NA") - day of creation date
c2=IF(A2<>"",MONTH(NOW()),"NA") - month of creation date

I copy the formula in b3:b150, c3:c150, and when i insert a new number in a3, the data from b2, c2 also changes (only c3 and b3 should've changed).

in relance sheet i'm not sure how i would do it, maybe

If(Day(now()) - b2 > 30, relance!a2 = case!a2,"NA")

 

[Diezz]

Since i have no answer yet i'll try to be more explicit and i'll concentrate only on my first problem.

I'm having where B1 = IF(A1>0,Now(),"NA")
B2 = IF(A2>0...) and so on, until B150 = IF(A150>0,Now(),"NA")

When i insert in A1 "1" then in B1 it appears the time, the problem is that when i update A2 = 2, b2 shows the actual time and b1 changes and shows the time when a2 was changed.

Everything changes in the B column when a single cell in A is changed.

Anyone has any idea?

Thanks

 

[walla07]

Hi Diezz,

The values update each time because you have a "live" expression in your formula, Now(). Every time you hit enter the whole sheet refreshes itself.

One solution is to copy each date and paste as values (under the Edit, Paste special menu), in the same cell, each time you add a new Case date. This will replace the formula with the actual date; you will lose the formula though.


Cheers

 

[Diezz]

Hello walla07,

Just testing if i understood well.

b1 = if(a1>0,Now(),NA)

If a1 = 1 then let`s say b1 = 10:04, 23.08.2006.

I can copy the data from b1 in c1 automaticaly?

 

[Fenrirshowl]

Diezz

In order to eliminate the "live" functions and not having to worry about pasting values I would suggest a Macro to fill in the day and month.

Assuming your titles are in row 2, the case number is going in column A and that you want the day in column B and month in column C:

Right click the tab of the "Case" worksheet.
Select View Code...
<You're now VBA>
Above the large window you will see a dropdown box with "General" in. Exter this dropdown and select "worksheet". Then copy the following in:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Row < 2 Or ActiveCell.Column > 1 Then Exit Sub

ActiveCell.Offset(-1, 1) = Day(Now())
ActiveCell.Offset(-1, 2) = Month(Now())

End Sub

The IF statement tells it to exit the macro if the row number is 1 or 2 and the column isn't A (referenced via the "1"). Change these as required.

The offset function gives the number of rows and columns relative to the current cell, where the results should be entered. (-1,2 means 1 row up and 2 columns across). If you have the cursor tab right under your options, rather than the normal down, when you press return there will be a slight amendment to the code needed.

This should work, or give you enough of a pointer on how to change it to your own needs.

Rich

 

[Fenrirshowl]

Sorry - it should have been AND rather than OR.

Note that the macro fills in the values depending on the change of cell selection. If you wanted to amend it so it only worked after a value had been entered you can check the last cell via

If ActiveCell.Row < 2 AND ActiveCell.Column > 1 Then Exit Sub
If activecell.offset(-1,0)="" or actviecell.offset(-1,1)<>"" or activecell(-1,2)<>"" then exitsub.
ActiveCell.Offset(-1, 1) = Day(Now())
ActiveCell.Offset(-1, 2) = Month(Now())

This will make the code not run if there is no date in the cell you've just left, or if entires are already in place.

 

[Diezz]

Quick question,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Row < 4 And ActiveCell.Column Is Not 3 Then Exit Sub
If ActiveCell.Value <> " " Then Exit Sub
If ActiveCell.Offset(0, -1) <> "" Then Exit Sub
ActiveCell.Offset(0, -1) = Day(Now()) & "/" & Month(Now())
End Sub

I would like "ActiveCell.Offset(0, -1) = Day(Now()) & "/" & Month(Now())" to take effect only if the user writes something in the column "B", startin from row4.

Anyone has an idea?