empty
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.
ActiveData 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 ActiveData functions are available:
Querying Worksheets
Example
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.
ActiveData 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 ActiveData 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. ActiveData.xlsx) with the path removed
- adWorkbookPathAndName() - returns the full path and name of the workbook. eg. C:UsersJohnExcelActiveData.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
Querying Worksheets
Example
- Open ActiveData's Sample Workbook
- Select the Invoices Sheet
- Select Query by Formula from the ActiveData Menu
- In this simple example click on Amount in the Columns area. Then click on the > sign and enter the number 10000.
- Click Finished
