SQL-WHERE TYPE Formulas in Excel
Let say I have a sheet in with columns Customer and CreatedDate with lots of row with data. Anyone who knows how to setup (through VBA or Formula) a second sheet that displays rows from the first sheet based on certain where statements, i.e. all rows with customers "created this month." (similar to a select ... where query against a SQL database).
There isn't an exact equivalent to the SQL select ... where functionality in Excel, but take a look at the VLOOKUP function. It may be what you are looking for. If that doesn't have enough functionality, you will probably have to use VBA:
Dim DataRange as Range Dim RowNum as Integer Dim NewRow as Integer Dim TestMonth as Integer Dim ThisMonth as Integer Set DataRange = Range(Sheet1.Cells(1,1), Sheet1.Cells(100,2)) ThisMonth = Application.WorksheetFunction.Month(Application.WorksheetFunction.Today()) NewRow = 1 For RowNum from 1 to DataRange.Rows.Count TestMonth = Application.WorksheetFunction.Month(DataRange.Cells(RowNum, 1).Value) if TestMonth = ThisMonth Then Sheet2.Cells(NewRow, 1).Value = DataRange.Cells(RowNum, 2).Value NewRow = NewRow + 1 End If Next RowNum
You could create a copy of the main data sheet (either by copying the sheet, or using a =Sheet1!A1 type formula filled across and down), and then use an Autofilter to narrow it down to the rows you require.
Here is an example using ADO.
Dim cn As Object Dim rs As Object 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 * FROM [Sheet1$] WHERE CourseKey=484" rs.Open strSQL, cn Worksheets(2).Cells(2, 1).CopyFromRecordset rs
Further information: http://support.microsoft.com/kb/246335
You can create a Pivot Table out of your data, then slice-n-dice it lots of ways.