Excel is, and always will be, a powerful tool for the financial team. With so much power, there are many great Excel formulas that rarely get used. I recently had a situation where I wanted to easily replicate a sheet and have each sheet use an MDX expression that referenced a different dimension name. I wanted to use a formula to determine the sheet name and used the CELL function.

The CELL function returns information about a specific cell within an Excel file. The syntax of the function is

CELL( info_type, reference ) 

The info_type defines the type of information to be returned and reference is associated with a specific cell. Some examples of info_type include “col” to define the column, “row” to define the row, “address” to define the cell refence and “filename” to return the full path and filename of the workbook and the worksheet.

The resulting format of the info_type called filename is

path[workbook.xlsm]sheetname

I took the approach of naming the sheet with the same name as the dimension. For example, one sheet was called “Account” and another sheet was called “Company.” By using a combination of the FIND function, the MID function and the TRIM function I was able to isolate the sheet name.

=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename"))+1,99))

I created this formula in cell B10 and then used the result within my MDX expression.

="{ TM1DRILLDOWNMEMBER( { ["&B10&"].[Orphans] }, ALL, RECURSIVE ) }"

This approach allows me to replicate an Active Form report by simply copying the entire sheet and then renaming the new sheet to be the same as the dimension name.

IBM Planning Analytics, which TM1 is the engine for, is full of new features and functionality. Not sure where to start? Our team here at Revelwood can help. Contact us for more information at info@revelwood.com. And stay tuned for more Planning Analytics Tips & Tricks weekly in our Knowledge Center and in upcoming newsletters!

Read more Excel-related tips & tricks:

IBM Planning Analytics Tips & Tricks: Recalculating Excel Worksheets

IBM Planning Analytics Tips & Tricks: Workarounds for Unsupported Excel Functions in TM1 Web

IBM Planning Analytics Tips & Tricks: New Excel Feature – XLOOKUP