Create a member set in Excel to define the members returned on an axis based on cell contents.

This can be more flexible than defining the members for a hierarchy in Excel. It lets you choose which combinations of members are shown instead of the grid doing a cross-join, which is the standard behavior.

You can leave a cell blank to get the default member of a hierarchy.

You must define the hierarchies and the members, allowing formulas to update the grid layout if required.

Example

Here, we want to display the Actuals for 2020 and the budget for 2021. This will give us the relevant numbers for the past year and avoid showing ‌empty columns for the year to come. We have set up an Excel range that contains the members we want returned. This will serve for later selections.

To do this:

  1. Navigate to the Anaplan XL Grid ribbon option. Select Keep > Define Member Set in Excel option.
    The selected Excel cell's position controls the defined axis. If the cursor isn't in the Column or Rows members area, columns will be chosen as the default.
  2. Name your set. Select the hierarchies and members within the report within the Design Set window.
    The grid is now set up to display the members you define in Excel.

You can change the members to display by over-typing the Excel cells. Formulas are supported so you can set up calculations to adjust the members displayed automatically.