Lookup or Index?!!!

[CrazyAMonkey]

Hello Excel Gurus!!

I am looking for some assistance to help my wife as the task i have been set is beyond me :/

I am looking to create a summary in a separate tab that show what jobs have been dispatched via 'Dispatched = Yes' from the two source pages.

I would then like Excel to transfer the column information from tabs one and two into the Overview sheet under the appropriate headings.







I have had limited success with formulas such as (references are all wrong, i was playing on another book);

(=INDEX($A$2:$C$7, SMALL(IF($B$9=$A$2:$A$7, ROW($A$2:$A$7)-MIN(ROW($A$2:$A$7))+1, ""), ROW(B1)),COLUMN(B1)))

I have scoured the internet looking for a solution but have not found the optimal method yet, can anyone shed some light please?!!

Thank you very much for reading,
A very frustrated spreadsheet noobie!

 

[CrazyAMonkey]

Bump!

 

[Jerry Sullivan]

Hi CrazyAMonkey,

A VBA approach is probably a better fit than a formula for this application.

Here's one way to do what you describe using VBA....

Sub QuerySummaryData()
'---Uses query to copy summary fields from records
'      meeting criteria from two worksheets in same workbook.

    Dim oConnection As Object
    Dim oRecordset As Object
    Dim sSQL As String
    
    sSQL = Join$(Array( _
        "SELECT T1.Client, T1.[Dispatch Date], T1.Deadline, T1.Notes", _
        "FROM [Source Month 1$] T1", _
        "WHERE T1.Dispatched='Yes'", _
        "UNION ALL", _
        "SELECT T2.Client, T2.[Dispatch Date], T2.Deadline, T2.Notes", _
        "FROM [Source Month 2$] T2", _
        "WHERE T2.Dispatched='Yes'", _
        "ORDER BY Client" _
        ), vbCr)
   
    Set oConnection = CreateObject("ADODB.Connection")

    With oConnection
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Extended Properties").Value = "Excel 8.0"
      .Open ActiveWorkbook.FullName
    End With
         
    Set oRecordset = CreateObject("ADODB.Recordset")
    
    oRecordset.Open Source:=sSQL, _
        ActiveConnection:=oConnection, _
        CursorType:=3, _
        LockType:=1, _
        Options:=1

    With Sheets("Overview")
        '--clear any existing summary data
        .Range("2:" & Rows.Count).ClearContents
        .Range("A2").CopyFromRecordset oRecordset
    End With

    oRecordset.Close
    oConnection.Close
    Set oRecordset = Nothing
    Set oConnection = Nothing
End Sub

 

[CrazyAMonkey]

Hello JS411, thank you very much for your reply,

What can i say - Holy sh'mokes!! Well i certainly didn't expect a programming reply! How would i implement such a solution? Does it work internally through Excel or does it require a VB plugin?

Also, can you recommend a site to teach me what each section actually means? If i understand it i will be able to use it better :D

Cheers

 

[Jerry Sullivan]

The VBA code works with the standard Excel program - no plug-in's are required.

1. Copy the code from Post #3, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog.
9. Double Click the macro's name to Run it.

If you're new to VBA code, I wouldn't recommend using this example for learning. I'll be glad to explain any parts of the code if you have some familiarity with the basics.

There are a lot of resources out there for learning VBA. hiker95 maintains a list of resources that he occasionally posts.

Here is a recent update.
http://www.mrexcel.com/forum/excel-...os-visual-basic-applications.html#post3518981