Date/Lookup/Reference Help

[speth]

I have a calc sheet with several items, but for brevity's sake, I have dates for the entire year listed down Column A, and values corresponding down Column B.

I'm trying to call up a reference from another cell, say C1, that will say either "given XXX date, the Column B value is YYY", where I can input the date that's being searched for in C2, or I can have it reference NOW().

I'm at a loss for this one, not even sure what angle to approach it from. Any suggestions?

 

[khan_safi773]

Thanks again, finally i done that.

 

[khan_safi773]

In E2 enter the formula I posted without the curly braces ({}) then hold down the shift and ctrl keys and press Enter - you should now see that Excel has added the curly braces. Copy that formula down col E to cover all the Items in col D.

thanks again Mr. JoeMo & Mrexcel, great full to you, it's really helpful to me finally i have done it,

once again, can you tell me? how can i do it through VBA macro in Excel.

 

[JoeMo]

thanks again Mr. JoeMo & Mrexcel, great full to you, it's really helpful to me finally i have done it,

once again, can you tell me? how can i do it through VBA macro in Excel.

Assuming your data are arranged as shown in Post #8, this macro will return the output you want in cols D & E (those columns should be empty before you run the macro.).

Sub FindLastDate()
'Assumes Items in col A starting in A2, Issue Dates in col B
'Output will be in cols D & E
Dim lRA As Long, lRD As Long, Ra As Range, Rb As Range
lRA = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("A1:A" & lRA).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
lRD = Range("D" & Rows.Count).End(xlUp).Row
If lRD = 1 Then Exit Sub
Range("E1").Value = "Last Date"
Set Ra = Range("A2:A" & lRA)
Set Rb = Ra.Offset(0, 1)
Range("E2").FormulaArray = "=MAX(IF(" & Ra.Address & "=D2," & Rb.Address & "))"
With Range("E2:E" & lRD)
    .FillDown
    .Value = .Value
End With
Columns("D:E").AutoFit
Application.ScreenUpdating = True
End Sub

 

[khan_safi773]

Assuming your data are arranged as shown in Post #8, this macro will return the output you want in cols D & E (those columns should be empty before you run the macro.).

Sub FindLastDate()
'Assumes Items in col A starting in A2, Issue Dates in col B
'Output will be in cols D & E
Dim lRA As Long, lRD As Long, Ra As Range, Rb As Range
lRA = Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Range("A1:A" & lRA).AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("D1"), unique:=True
lRD = Range("D" & Rows.Count).End(xlUp).Row
If lRD = 1 Then Exit Sub
Range("E1").Value = "Last Date"
Set Ra = Range("A2:A" & lRA)
Set Rb = Ra.Offset(0, 1)
Range("E2").FormulaArray = "=MAX(IF(" & Ra.Address & "=D2," & Rb.Address & "))"
With Range("E2:E" & lRD)
    .FillDown
    .Value = .Value
End With
Columns("D:E").AutoFit
Application.ScreenUpdating = True
End Sub

Thanks again,
Once again new problem, when i updated new data it will not work automatically ?

 

[JoeMo]

Thanks again,
Once again new problem, when i updated new data it will not work automatically ?

You can adapt the code I posted to a worksheet_change event whenever a change is made to col B.

 

[khan_safi773]

Thanks, Mr. JoeMo for your nice cooperation, Thanks, also mReXcel.com Once again new problem, how can i do it to using tow sheet. Like one sheet is for Data and another sheet for report. i need my data in report sheet. and it will work automatically?

 

[JoeMo]

Thanks, Mr. JoeMo for your nice cooperation, Thanks, also mReXcel.com Once again new problem, how can i do it to using tow sheet. Like one sheet is for Data and another sheet for report. i need my data in report sheet. and it will work automatically?

If you have already converted the code in post #14 to a worksheet_change event code, just copy and paste it to the second sheet's code module.

 

[khan_safi773]

If you have already converted the code in post #14 to a worksheet_change event code, just copy and paste it to the second sheet's code module.

thanks, I think i did not express clearly, I have some data in one sheet1, Like "Items" col A and "Issued Date" col B, i have another sheet name "Report", in report sheet col A i will write some "items" Name and Col B show last issued Date from sheet 1? hope u understand ? (USE in VBA)

 

[JoeMo]

thanks, I think i did not express clearly, I have some data in one sheet1, Like "Items" col A and "Issued Date" col B, i have another sheet name "Report", in report sheet col A i will write some "items" Name and Col B show last issued Date from sheet 1? hope u understand ? (USE in VBA)

This is sheet code for the Report sheet. Assumes the data for items and issue dates is in a sheet named "Sheet1". When an item name is entered in the Report sheet in col A, the last issued date for that item will be returned in col B of the Report sheet. Be sure to format col B as Date.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Not Intersect(Target, Columns("A")) Is Nothing Then
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    For Each c In Intersect(Target, Columns("A"))
        If Not IsEmpty(c) Then
            With c.Offset(0, 1)
                .FormulaArray = "=MAX(IF(Sheet1!A:A=" & c.Address & ",Sheet1!B:B,""""))"
                .Calculate
                .Value = .Value
            End With
        End If
    Next c
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End If
End Sub

 

[khan_safi773]

Thanks for Quick reply, Your so great also. May be I bother lot. I am new that why i understand very poor. your reply NO#14 work nicely but reply no#19 not work in my sheet, i coped that another sheet, but when i run it Excel reply Macros box, plz help me