How To: Use Cell References and Anchoring

Product:  BizExcelerator
Applies to:  V4-All
Article Type:  How To 

When creating a BizExcelerator report, you can always enter specific values into a parameter field within the Formula Editor.  But to make a BizExcelerator report more flexible and more easily updated, it is recommended that you use cell references and have the function use a value that is in a specific cell on the report. 

Instead of entering a hard value of 2009 for the Fiscal Year parameter, you could enter a cell reference for a cell on the report that contains a year.  Then, to quickly change the report so that it retrieves data for another year, you simply change the year in the cell you referenced in the function instead of having to open the function and make that change in the Formula Editor.
 
For example, the following function uses the cells C2 and C3 for the Fiscal Period and Fiscal Year respectively.  This means that if you change the value of the Fiscal Year in cell C2 to 2008, this BizExcelerator function will retrieve data for Period 2 of 2008 instead of for 2009 which was originally entered into the C2 cell.

 
Equal Sign ‘=’
Notice the equal sign “=” in the above example.  Any time you want to use a cell reference instead of a hard coded value for a parameter, you must precede the cell reference with an equal sign “=”. 

Important:  Once a cell reference has been used for a parameter’s value, BizExcelerator will always expect the value being entered is a cell reference and the “=” should not be re-entered.   In addition, when the Formula Editor is reopened, the “=” will not be displayed.

Anchoring
Another way to make your BizExcelerator report more powerful and flexible is to use Excel’s cell anchoring.  When a dollar sign “$” is used in front of the column letter, row number or both in a cell reference, that part of the cell reference is considered to be anchored.  When that cell’s function/formula is copied to other cells on the report, any parts of the cell reference that are anchored are not changed to reflect the new cell reference where that function has been copied.
 
For example, suppose you want a report that provides the balances for a certain list of accounts for a certain year and period like the following:

 
You could build the report with a function in each cell and hard code the period and the account number.  Or you could use cell references and anchoring to simplify building the report.

In this example, the following describe your requirements:
• You want each function to always get it’s year from cell C2. 
• You want each function to always get it’s period from Row 4 but pull from the  appropriate column as the period increases across the columns, and
• You want each function to always get it’s Main Account number from Column B but pull from the appropriate row as the accounts change down the rows.

To do this you would anchor the cell references based on which part of the cell reference you don’t want to change as the function is copied to other cells.

 
When finished, your Formula Editor would look like this:

Now, the function can be easily copied, with a drag and drop, to adjacent columns and rows and will update to reflect the correct row and column based on the cell referencing and anchoring used.  Opening the Formula Editor for one of the cells that was copied reveals that the non-anchored cell references changed appropriately and the anchored columns and rows did not change.

Remember:  When the Formula Editor is reopened, the “=” will not be displayed.
 
If the provided information does not resolve your question, please contact BizNet Software Support for further assistance.