Query By Formula

The Query By Formula command lets extract rows for a worksheet based on the formula you specify.

The formula is built by clicking on the available columns, buttons and functions.

To get help on a function, click on the Excel Help button.

Easy-XL provides a number of cell level worksheet functions that you can use in your formulas or directly from within cells in your worksheet.

The following Easy-XL functions are available:

  • adDaysDif(date1,date2) - this calculates the number of days difference between two date values
  • adQuarter(date) - this calculates the calendar quarter (1 - 4) of the specified date
  • adFiscalQuarter(date,startingmonth) - this specifies the fiscal year quarter (1 - 4) of the specified date value. The startingmonth value (1 - 12) specifies on which is the first month of the first quarter in the fiscal year.
  • adSOUNDEX(text) - returns the soundex code (1 letter followed by 3 digits) for the specified text. This function is used to match similar sounding names. More information about Soundex is available on Wikipedia.
  • adLIKE(text,pattern) - this returns True if the specified text matches the specified pattern. This function uses Visual Basic's Like operator for comparisons. Click here for the required syntax.
  • adWorkbookPath() - returns the name of the currently open workbooks path. The path is the folder in which the workbook resides.
  • adWorkbookName() - returns the name of the workbook (eg. Easy-XL.xlsx) with the path removed
  • adWorkbookPathAndName() - returns the full path and name of the workbook. eg. C:UsersJohnExcelEasy-XL.xlsx
  • adWorksheetName() - returns the name of the current worksheet
  • adWorksheetUsedRows() - returns the number of rows in the current worksheet
  • adWorksheetUsedColumns() - returns the number of columns in the current worksheet
  • adFontColor(column) (for sorting by the color #) returns the foreground font color index for the specified column on the currently selected row
  • adBackgroundColor(column) (for sorting by the bakground color number) returns the background color index for the specified column on the currently selected row

Example

  • Open Easy-XL's Sample Workbook
  • Select the Invoices Sheet
  • Select Query by Formula from the Easy-XL Menu
  • In this simple example click on Amount in the Columns area. Then click on the > sign and enter the number 10000.
  • Click Finished