Copies the formula in which the function is used to adjacent cells and resizes the table accordingly.

Syntax

If Formula returns a numerical value:

=Formula + MM_SETSIZE(NbRows, NbCols)

or if Formula returns a text:

=Formula & IF(MM_SETSIZE(NbRows, NbCols)=0,"",0)

• Formula
  The formula you want to copy to adjacent cells.

• NbRows
  The total number of rows on which the formula is copied (including the initial cell).

• NbCols
  The total number of columns on which the formula is copied (including the initial cell).

Remarks

• This function is not meant to be used inside another function. It must be added to the formula you want to copy by using the symbol "+".

• This function works in Excel too and automatically updates your cells. This is the equivalent of dragging the fill handle in Excel, except NbRows and NbCols can vary. However, in case you have formulas on the left and/or on top of your cell (see example 2), these formulas will not be copied in Excel as tables are not recognized the same way as in Mind (it will work in Mind).

• Where the formula has been copied, a "+0" is visible inside the formula. If you want to avoid this, you can add the MM_SETSIZE function after having already copied your formulas as the function will not overwrite existing formulas.

• Warning: In Excel, once a cell has been updated using this function, you may not be able to cancel this action.

• Warning: In Excel, be sure that the cells where the formulas must be copied are empty. The function does not overwrite existing formulas, especially if you update the formula that needs to be copied. They must be deleted beforehand.

• If you have numerical values in your headers as inputs (not formulas), the MM_SETSIZE function will add (if needed) headers by increasing the preceding header value by 1.

Example 1: Simple case with only one formula

Formula in cell A3: =A2*2+MM_SETSIZE(10,1).
The formula that will be copied is:

=A2*2

Once the formula is validated in Excel (in the formula bar), it is automatically copied in the next 9 rows.

The parameters of the MM_SETSIZE function can vary. In that case, if NbRows and/or NbCols are modified, the table is automatically resized :

• in Excel, after each change if automatic calculation is enabled,

• in Mind, after each run.

Example 2: With formulas on the left and on the top of the table

In this example, we want to create dynamic multiplication tables. From the parameters NbRows and NbCols, we define the size of the multiplication table. The difference with the previous example is that there are different formulas in the first row and the first column. We want them to be resized as well with the whole table.

The initial size of the table #Table is the following, with 10 rows and 2 columns (headers and first column are not counted): 

What was input in cell C7 is the formula: =B$6*$A7 + MM_SETSIZE(B2,B3).

As soon as the formula is validated in Excel, it is automatically copied in a matrix of size (10,2), including the initial cell C7.

The formulas for the first row and the first column of #Table must be filled separately as they are not automatically filled in Excel by the MM_SETSIZE function. It must match with the MM_SETSIZE function as showed above before uploading the model in Mind. Otherwise when changing the parameters of MM_SETSIZE, only the corresponding empty cells will be copied.

The result is the following:

After uploading the file in Mind, we choose the following parameters:

The table is automatically resized after running the model: 

N.B.: In this example, there was only one row on the top and one column on the left of the cell containing the MM_SETSIZE function. However, you are not limited to one row or one column. You can have as many rows and columns as you wish above and on the left of the MM_SETSIZE function.

Example 3: With /resizecolumn and /resizerow headers

What if you want to resize different tables with multiple formulas?

Solution 1: Similar to the second example but with multiple tables. For each table you want to resize, add a MM_SETSIZE function well positioned. Everything on the left and on top will be kept and resized based on that function.

Solution 2: Use only one MM_SETSIZE on a reference table and add headers /resizecolumn and/or /resizerow (or /resize) to every table you want to resize based on the reference table; /resizecolumn is used for dynamic columns and /resizerow (or /resize) for dynamic rows. This solution is faster to set up, especially if you have a lot of tables to resize.

Contrary to the MM_SETSIZE function where you can directly put the desired number of rows and columns, it is resizing tables by comparing their sizes to another tagged table. In other words, if some rows and/or some columns are added to that tagged table, then the same amount is added to the other table.

Let us consider the below example: 

• The size of #MM_SETSIZE is defined with the function MM_SETSIZE(A2,1) contained in the highlighted cell. It depends on the value chosen in table #NbRows. To be more precise, the formula is =C2 + 1 + MM_SETSIZE(A2,1).

• The size of #Table depends on the size of the table #MM_SETSIZE. Indeed, they are both tagged with /resize. 

In Mind, if we replace the value NbRows (10) by 12 and run the model, two rows will be added to #MM_SETSIZE and #Table. 

N.B. If you have several groups of tables that are resized based on different reference tables, you can use different names to tag them: for instance /resize.nameA, /resize.nameB, ...etc. To learn more about resizing, check this article about Grids dynamic resize‍ .