Column values based on entries on another sheet

[ChristineJ]

I have a list of statements in Column B of Sheet1. Users will enter an "a" in Column A for each statement that they want to appear on Sheet2, Column B.

Sheet1



Based on Sheet1 above, below are the results that I want to show in Sheet2.

Sheet2


I'd rather not have all the results from Sheet1 be on Sheet2 and then hide those without an "a" in Column A. I just want a list of those with in "a" in Column A on Sheet1.

Any ideas for doing this are appreciated!

[Oorang]

Hi Christine,
You don't really need VBA to pull this off (although you can).
Non-VBA Way:
1.) Data>Import External Data>Import Data>
2.) Change "Files Of Type" Drop down to "Excel Files (*.xls)"
3.) Select the workbook you are already in.
4.) Click Open
5.) You will be presented with a prompt for "Select Table", select the correct worksheet name (Ex:Sheet1$) and click OK.
6.) Click "New Worksheet"
7.) Click "Edit Query"
8.) (Assume your column with "a"s in it is named "Flag".)
9.) Change "Command Type" to SQL.
10.) Paste the Following:

Select * From [Sheet1$] Where [Flag] = 'a'

Here is the same way with VBA:
[vba]Public Sub Example()
Const strConnection_c As String = _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=xxx; " & _
"Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";" & _
"Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";" & _
"Jet OLEDB:Engine Type=35"
Dim ws As Excel.Worksheet
Set ws = ThisWorkbook.Worksheets.Add
ws.Name = "Just A"
With ws.QueryTables.Add(Replace(strConnection_c, "xxx", ThisWorkbook.FullName), _
ws.Cells(1, 1), "Select * From [Sheet1$] Where [Flag] = 'a'")
.Refresh BackgroundQuery:=False
End With
End Sub[/vba]