|
You can write Excel formulas with SQL 2 Excel. There are 4 special substitution variables that you can use in your formulas:
{ROWID} : Substituted with current row ID
{COLID} : Substituted with current column ID
{MINROWID} : Substituted with the minimum row ID, e.g. first data row (added in version 1.1.5)
{MAXROWID} : Substituted with the maximum row ID, e.g. last data row (added in version 1.1.5)
You can use simple expressions, like below, to reference rows or columns relative to the current.
{ROWID-1} : Will be substituted with current row ID - 1
{ROWID+1} : Will be substituted with current row ID + 1.
Here's an example illustrating how to write the SQL query:
SELECT ID, '=A{ROWID}*2' AS FORMULA
FROM jos_content
You need to specify which column(s) that does have formulas in them in the Worksheet configuration. In this case it's column 2 which will have a formula. We specify that in the Worksheet configuration, like this:
If more then one column has formulas in them, specify the columns with a comma delimted list.
The {ROWID} will be substituted with the current row ID when the cell formula is inserted into the Worksheet. The resulting spreadsheet will contain formulas, like this:
 |
Subscribe to this comment's feed
