Hi All,
Can any one help me on this I have 2 sheets in a work book, In sheet1 I will
be entering the numbers in a column as follows: -
A
68504
69272
68493
68325
68323

now my query is I will be entering the data in sheet2 as follows
A
59944
67331
68323
70778
68504

If the number entered in column A of sheet1 is avilable in column A of
sheet2 I want out put as follows in sheet2

A B C
59944 Na -
67331 Na -
68323 Yes current date
70778 Na
68504 Yes current date

Note the numbers entered in column A of sheet1 may not be in sorted order it
will be in random wise.

TIA

Use the VLOOKUP function.

"kiran" wrote:

Hi All,
Can any one help me on this I have 2 sheets in a work book, In sheet1 I will
be entering the numbers in a column as follows: -
A
68504
69272
68493
68325
68323

now my query is I will be entering the data in sheet2 as follows
A
59944
67331
68323
70778
68504

If the number entered in column A of sheet1 is avilable in column A of
sheet2 I want out put as follows in sheet2

A B C
59944 Na -
67331 Na -
68323 Yes current date
70778 Na
68504 Yes current date

Note the numbers entered in column A of sheet1 may not be in sorted order it
will be in random wise.

TIA

Hi, try this.
In cell B1 of sheet 2, insert the following, then fill down:
=IF(COUNTIF(Sheet1!$A$1:$A$5,A1)0,"Yes","na")
In cell C1 of sheet 2, insert the following, then fill down:
=IF(B1="Yes",TODAY(),"")

If you want the date to be fixed, and not always show the current date, you
would need to use an event macro, or a macro one you run manually.
Regards - Dave.

Thanks Dav,
Can i have a macro to fix the date

TIA

"Dave" wrote:

Hi, try this.
In cell B1 of sheet 2, insert the following, then fill down:
=IF(COUNTIF(Sheet1!$A$1:$A$5,A1)0,"Yes","na")
In cell C1 of sheet 2, insert the following, then fill down:
=IF(B1="Yes",TODAY(),"")

If you want the date to be fixed, and not always show the current date, you
would need to use an event macro, or a macro one you run manually.
Regards - Dave.

Hi,
Try this.
Right-click the sheet tab, then copy and paste the following into the sheet
window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Variant
On Error Resume Next
Application.EnableEvents = False
If Intersect(Target, Range("C1:C1000")) Is Nothing Then Exit Sub
A = Target.Value
If Target = Date Then Target.Value = A
Application.EnableEvents = True
End Sub

Change the range "C1:C1000" to suit your data.

"kiran" wrote:

Thanks Dav,
Can i have a macro to fix the date

Hi Dave,
thanks for your swift responce i copyed & pated the code while executing no
change want to do ne change

"Dave" wrote:

Hi,
Try this.
Right-click the sheet tab, then copy and paste the following into the sheet
window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Variant
On Error Resume Next
Application.EnableEvents = False
If Intersect(Target, Range("C1:C1000")) Is Nothing Then Exit Sub
A = Target.Value
If Target = Date Then Target.Value = A
Application.EnableEvents = True
End Sub

Change the range "C1:C1000" to suit your data.

"kiran" wrote:

Thanks Dav,
Can i have a macro to fix the date

Hi Kiran,
Sorry, but I can't understand your response.
Dave.

"kiran" wrote:

Hi Dave,
thanks for your swift responce i copyed & pated the code while executing no
change want to do ne change

"Dave" wrote:

Hi,
Try this.
Right-click the sheet tab, then copy and paste the following into the sheet
window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim A As Variant
On Error Resume Next
Application.EnableEvents = False
If Intersect(Target, Range("C1:C1000")) Is Nothing Then Exit Sub
A = Target.Value
If Target = Date Then Target.Value = A
Application.EnableEvents = True
End Sub

Change the range "C1:C1000" to suit your data.

"kiran" wrote:

Thanks Dav,
Can i have a macro to fix the date