Did you know you can create Excel formulae as data expressions in the Administrator module? This can be very useful if you have a Formula you need to be extended as far down the sheet as the rest of the data.
How to create an Excel Formula in your Report
- Open your Administrator Module and Browse to the container in which you want to add the Excel Formula
- Right Click on the Container and select Add Expression
3. Select Excel Formula option
4. Give the Excel Formula a name e.g. “GrossProfit” then type in the Excel Formula (Do Not put an = sign in front of the formula, Alchemex automatically puts it in when you run the report), click OK
5. Open your Report Manager and Browse to the Report associated to the above container and Double click on it
6. Right Click on Columns and select Add Columns
7. Select the Excel Formula you created and click OK
8. Run the report out, you will see your Excel Formula in the Data sheet (e.g. Sheet1)
Notes
- If you create an Excel Formula with a specific cell reference (e.g. Sheet1!A2) it will not be changed as the Formula moves down the rows, (e.g. All the rows Formula will refer to Sheet1!A2). Alternatively you can use the Column reference Sheet1!A:A
- It is advisable to use Named ranges in your Excel Formula
- Alchemex automatically creates a Named Range for each Column in the Data Sheet of a Report based on the name of the Column
Tip
It is easier to create the Excel Formula on the Data Sheet of a run out report and then copy the Formula back to the Administrator Module
