Solved: Compare column with multiple sheets

[tlchan]

Hi there,
I wish to compare column A of "Summary Year" sheet with the remaining sheets in the workbook in column A for matched value. If matched then copy the respective row value under column D to "summary Year" sheet column B and so forth. The column value under various sheets may not in same sequence as in "summary Year" sheet.


How do I accomplish this task?


Example of workbook attached

Thank you
:(

[mdmackillop]

Here's a UDF solution
Option Explicit

Function GetData(typ As Range, Dt As Range)
Dim sh As String, c As Range

sh = Format(Dt, "dd-MMM-yy")
On Error Resume Next
Set c = Sheets(sh).Columns(1).Find(typ, lookat:=xlWhole)
If Not c Is Nothing Then
GetData = c.Offset(, 3)
Else
GetData = "-"
End If
End Function

[tlchan]

Thanks mdmackillop. Your code works brilliant!

How should I put it under command button where the function will be appended automatically at respective columns and at the same time extract value from respective sheet name under Row 1 of column B,C,D....

Please help!
:dunno

[marreco]

Hi...
maybe this will help

Private Sub workbook_Open()

Range("B2").FormulaLocal = "=getdata($A2;B$1)"
Range("C2").FormulaLocal = "=getdata($A2;C$1)"
Range("D2").FormulaLocal = "=getdata($A2;D$1)"
End Sub

[mdmackillop]

or

Sub AddFmla()
Selection.FormulaR1C1 = "=GETDATA(R[]C1,R1C[])"
End Sub

[tlchan]

[/URL]
Thanks both of you for the quick solution.

The code works perfectly.
[URL="http://www.vbaexpress.com/forum/member.php?u=87"] (http://www.vbaexpress.com/forum/member.php?u=37997)