| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
show date only if information entered in cell
I want to have the current date show up in the first row say A whenever
someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Hi,
Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 3 Then If Target.Value < "" Then Target.Offset(, -2).Value = Date Else Target.Offset(, -2).Value = "" End If End If End Sub Mike "digitalmuse" wrote: I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Put this in A4:
=IF(C4="","",TODAY()) Format the cell as date. Hope this helps. Pete On Oct 17, 2:26*pm, digitalmuse wrote: I want to have the current date show up in the first row say A whenever someone enters information in a row - *in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Hi,
you could try the following formula in cell A4: =if(C4<"",today(),"") -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "digitalmuse" wrote in message ... I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Thank you so much works like a charm
"Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 3 Then If Target.Value < "" Then Target.Offset(, -2).Value = Date Else Target.Offset(, -2).Value = "" End If End If End Sub Mike "digitalmuse" wrote: I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
thanks for the responses and i guess i should have been clearer, the formula
cant depend on input to cell c4 only it needs to apply to any entry made in column C "Ashish Mathur" wrote: Hi, you could try the following formula in cell A4: =if(C4<"",today(),"") -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "digitalmuse" wrote in message ... I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
which i believe is why the formula now is not working it worked up until row 3
now anything entered in column c after row 3 does not result in a date entry in colum a "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 3 Then If Target.Value < "" Then Target.Offset(, -2).Value = Date Else Target.Offset(, -2).Value = "" End If End If End Sub Mike "digitalmuse" wrote: I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Try it this way, then:
=IF(COUNTA(C:C)0,TODAY(),"") Hope this helps. Pete On Oct 17, 3:39*pm, digitalmuse wrote: thanks for the responses and i guess i should have been clearer, *the formula cant depend on input to cell c4 only it needs to apply to any entry made in column C |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Hi,
It's written to only work on column C (3) If Target.Column = 3 Then If you want to work on a different column change the 3 and the offset to 1 less than the column number. IF you want it to work on a number of columns then that's a slight re-write Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Not Intersect(Target, Range("A:F")) Is Nothing Then Application.EnableEvents = False If Target.Value < "" Then Cells(Target.Row, 1).Value = Date Else Cells(Target.Row, 1).Value = "" End If Application.EnableEvents = True End If End Sub Mike "digitalmuse" wrote: which i believe is why the formula now is not working it worked up until row 3 now anything entered in column c after row 3 does not result in a date entry in colum a "Mike H" wrote: Hi, Right click your sheet tab, view code and paste this in Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column = 3 Then If Target.Value < "" Then Target.Offset(, -2).Value = Date Else Target.Offset(, -2).Value = "" End If End If End Sub Mike "digitalmuse" wrote: I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
It does work in a cell - im trying now to get it to work in all the cells in
that column(range) thanks for the assist ! "Pete_UK" wrote: Try it this way, then: =IF(COUNTA(C:C)0,TODAY(),"") Hope this helps. Pete On Oct 17, 3:39 pm, digitalmuse wrote: thanks for the responses and i guess i should have been clearer, the formula cant depend on input to cell c4 only it needs to apply to any entry made in column C |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Hi,
Try something like this: =IF(COUNTA(B1:IV1)0,TODAY(),"") -- Thanks, Shane Devenshire "digitalmuse" wrote: I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
that works perfect in one cell what would i add or modify to have it apply to
an entire colum(or named range) i.e. range is named entryDate and covers column A "Pete_UK" wrote: Put this in A4: =IF(C4="","",TODAY()) Format the cell as date. Hope this helps. Pete On Oct 17, 2:26 pm, digitalmuse wrote: I want to have the current date show up in the first row say A whenever someone enters information in a row - in other words if there is data in cell C4 then the current date should show up in row A4 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
show date only if information entered in cell
Do you realize that the TODAY() function is volatile and will change when
you open the workbook tomorrow? I would go with Mike's event code to add a static date in column A whenever a cell in Column C is changed. Gord Dibben MS Excel MVP On Fri, 17 Oct 2008 08:53:01 -0700, digitalmuse wrote: It does work in a cell - im trying now to get it to work in all the cells in that column(range) thanks for the assist ! "Pete_UK" wrote: Try it this way, then: =IF(COUNTA(C:C)0,TODAY(),"") Hope this helps. Pete On Oct 17, 3:39 pm, digitalmuse wrote: thanks for the responses and i guess i should have been clearer, the formula cant depend on input to cell c4 only it needs to apply to any entry made in column C |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| To show month end date from any date entered | Excel Discussion (Misc queries) | |||
| Can Excel warn me if a cell already contains entered information? | Excel Worksheet Functions | |||
| Freeze Cell Once Information is Entered | Excel Discussion (Misc queries) | |||
| Date subtraction -How to not show negative when 2nd date not entered | New Users to Excel | |||
| How do I get a cell to show the day of the week when date entered | Excel Discussion (Misc queries) | |||
Linear Mode
