Marlett check a date to another sheet

niailmar

New member
Joined
Apr 4, 2012
Messages
30
Reaction score
0
Points
0
I need an expert like you to solve my problem.

I have a Attendant Table for Staff, which consists of Name, Staff ID and so on.
I have already create the code for “Automatic appearance of date in Column D when you Double Click the Column C of attendance column “. The code is like this:


Code:
Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub
    'Isolate Target to a specific range
   If Intersect(Target, Range("CheckBoxs")) Is Nothing Then Exit Sub
    'Set Target font to "marlett"
    [URL="http://target.font.name/"]Target.Font.Name[/URL] = "marlett"
    'Check value of target
    If Target.Value <> "a" Then
        Target.Value = "a"    'Sets target Value = "a"
        Cancel = True
        Exit Sub
    End If
    If Target.Value = "a" Then
        Target.ClearContents    'Sets target Value = ""
        Cancel = True
        Exit Sub
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'Limit Target count to 1
    If Target.Count > 1 Then Exit Sub
    'Isolate Target to a specific range
   If Intersect(Target, Range("CheckBoxs")) Is Nothing Then Exit Sub
    'Select a specific subset of the range "Ckboxes"
    Select Case Target.Address
    Case Else

        If Target.Value = "a" Then
            Target.Offset(0, 1).Value = Date
        Else:

            Target.Offset(0, 1).Value = ""
        End If
    End Select

Name and staff id already available in sheet2. Sheet2 is the database that will be store all the information. Date that will captured is current date(today date) when double click the Column C of attendance column then date will appear. I will clicking in the row at attendance column. The tick is create by using marlett function. You can refer the coding above.The action should be done is like this, if sheet2 have similar name and staff id like in sheet1 then date is should copy or ??? in sheet2 (column date)



I hope you can help me bout this problem. I attach an image to clear what I need.
I appreciate your help.
Marlett.jpg

TQ
 
Hi,
Make it simple. You don't need VBA.
Select B2:B5, change text to Marlett. Type lower case "a" to have tick appear.
On the cell D2 enter formula =IF(C2="a",TODAY(),""). As soon as you type "a" lower case on column C will show as tick, but in fact that is "a", so formula will add a date anytime has "a" on the column C.
On the sheet2 cell C2 all you need is formula =Sheet1!D2. Drag this formula down and you are good to go.
Note: If sheet1 & 2 are not aligned let me know. There is a different formula for that. Also after you put a tick if you need to make dates values only let me know...because formula Today() will change your date every day as soon as you open your file.
Thanks
 

Attachments

  • Tick File.xlsx
    8.8 KB · Views: 28
hi Jim..

Thanks for the formula. It really work on it.
But I have a problem with it. I want the date captured is same with the date of ticked. The formula that have u given just is captured the same date. I want it will be execute like below:

If the user have tick today, so it will captured today date (11 July 2012).
But if the user have tick for tomorrow, so it will capture the date of tomorrow. (12 July 2012).

Thank so much!

-nia-
 
Nia, are you saying you have the dates on the sheet2, and you want to bring that date to sheet1 when you tick the name?
 
Nia, are you saying you have the dates on the sheet2, and you want to bring that date to sheet1 when you tick the name?

It's okay Jim.
I've already solve my problem.
Sorry take your time. Thank so much :)
 
Nia, are you saying you have the dates on the sheet2, and you want to bring that date to sheet1 when you tick the name?

Jim, based on excel that you attached, what I mean is:

1) On sheet1, when user tick at column attendance on 12 July 2012, the column of date will show date of 12 July 2012. Same with Sheet2, also appear date of 12 July 2012.

2) But, when user tick at column attendance on 15 July 2012 (Sheet1), the column of date will show date of 15 July 2012. Same with sheet2, also appear date of 15 July 2012.

can you understand with my explanation?
 
so Nia,
are you ok now, or do you want me to review?
 
Yes I'm okay Jim. Sorry to make you confuse. Can you review again about that?
I'm really need your help.
 
Yes I'm okay Jim. Sorry to make you confuse. Can you review again about that?
I'm really need your help.

OK here how I understand. On the sheet1 you have entered different dates including future dates (column D). When booking has been confirmed with client you want to mark with a tick on sheet1 column C. Now same date you want to appear on sheet 2 for all ticked on sheet 1. See attached for worksheet with modified formulas to meet your needs. I hope this works for you. If not let me know we can modify again until it is right. Good luck!
 

Attachments

  • Tick File (1).xlsx
    9.1 KB · Views: 29
Yes I'm okay Jim. Sorry to make you confuse. Can you review again about that?
I'm really need your help.

OK here how I understand. On the sheet1 you have entered different dates including future dates (column D). When booking has been confirmed with client you want to mark with a tick on sheet1 column C. Now same date you want to appear on sheet 2 for all ticked on sheetView attachment Tick File (1).xlsx1. See attached for worksheet with modified formulas to meet your needs. I hope this works for you. If not let me know we can modify again until it is right. Good luck!
 
OK here how I understand. On the sheet1 you have entered different dates including future dates (column D). When booking has been confirmed with client you want to mark with a tick on sheet1 column C. Now same date you want to appear on sheet 2 for all ticked on sheetView attachment 6971. See attached for worksheet with modified formulas to meet your needs. I hope this works for you. If not let me know we can modify again until it is right. Good luck!


thank you so much!
really appreciated this! :)
 
Back
Top