Using Excel Cell References with Cubix Functions

Once you have created your basic Get Cell function, you can enhance its capabilities by using references to Excel cells in place of the ‘hard coded’ parameters.

Starting with the following formula:
=TCM_GCV8("Main","France","HISTORY","LocalCurrency","Input","Jan","PL.10020","Total","Value")

The Excel template will show the value for January. But what if you wanted to show the value for February or March or so on in adjacent columns? You would need to create further formulas referencing those particular months.

Rather than writing a news formula for each cell, the one above can be adjusted so that instead of the fixed reference to “Jan”, it would refer to a cell in the workbook. Then depending on the contents of the referenced cell, the TCM function would retrieve different data.

Get Cell formula with fixed parameters

The formula would now look like this:
=TCM_GCV8("Main","France","HISTORY","LocalCurrency","Input",B2,"PL.10020","Total","Value")

By referencing cell B2, the formula can be simply copied across into cells C4 to G4 and each time it will retrieve data for the month as defined in row 2

Get Cell formula with referenced paramters

Any of the parameters that make up the TCM Get Cell formula can be replaced with Excel cell references. Whenever the contents of one of the referenced cells changes, any formulas that use that reference will update and retrieve new values.

*Cell references can be the simple address of the cell, e.g. B2, can include fixing of rows and/or columns, e.g. B$2 or $B$2, can be on other sheets, e.g. Sheet2!$B$2, or can be defined range names, e.g. MthName

!When using Cubix names directly in a formula, the name should be enclosed in double quotation marks (“Jan”). Cell references or range names should not have quotation marks.