matching cells in one column with another column in another sheet

Question

I have 2 sheets - one has one column for date and another for the amounts and the other sheet has a column for the date and the amounts plus another column that has description of each amount. *How can match these 2 columns of amounts**? I want a formula taht tells me which cell on the first sheet I can find a certain amount that also exists on the other sheet.

Thanks a lot if someone can help me

Answer 1

If you are trying to reference a cell in another sheet, you use the following:

='SheetName'!F2

where F2 is the cell you want to retrieve the value of.

Answer 2

How about ADO?

Sub ListMatches()
Dim cn As Object
Dim rs As Object

    'http://support.microsoft.com/kb/246335 '

    strFile = Workbooks(1).FullName
    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
        & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")

    cn.Open strCon

    strSQL = "SELECT s2.AcDate, s2.Amount, s1.Description " _
    & "FROM [Sheet2$] s2 INNER JOIN [Sheet1$] s1 " _
    & "ON s2.AcDate=s1.AcDate AND s2.Amount=s1.Amount"

    rs.Open strSQL, cn, 3, 3

    Worksheets(3).Cells(2, 1).CopyFromRecordset rs

End Sub

Answer 3

I'm not sure I understand your question, but it sounds like you want to look at the MATCH and/or VLOOKUP worksheet functions. (MATCH can tell you the position of a specific value in a list of values, and VLOOKUP can find a value in a column, and then give you the value from the same row in a different column.)