Setup an Excel template so calculations are not dependant on a specific number of columns / rows

Question

Problem Statement:

I'm creating a template for multi tiered complicated calculations in MS Excel that depend on a few input "n x 3" matrices.

It is really difficult to redesign the 15 sheets or so (200 ~ 300 lines each) every time I have a different "n" where "n" ranges from 3 to 900.

Goals:

• I'd like to input the number of "n" in a cell subsquently changing all the other sheets in the workbook accordingly.
• Avoid VBA as much as possible

How can I achieve these goals?

Note: I'm willing to answer any questions or comments concerning my issue

EDIT "n" represents the number of columns / rows, if n = 3, all calculations will be for a 3 x 3 matrix. If n = 500, all calculations will be for a 500 x 3 matrix. I want Excel to do the expansions / contractions of the rows automatically, so i do't have to do them myself accross hundreds of tables

Answer 1

Instead of calculating agains A1:A300, you can calculate against A:A, which is the entire column.

You may have to bear in mind that you don't want other stuff in that column when you design your layout.

Answer 2

Create defined names (Insert - Name - Define) that use OFFSET and COUNTA to make dynamic ranges. Instead of

=SUM(A1:A300)

use

=SUM(MyRange)

where MyRange is

=OFFSET($A$1,0,0,COUNTA($A:$A),1)

See also

Answer 3

In Excel 2007 turn your data matrices into tables.

This can be done by clicking on a matrix and then on the Insert tab select Table. The keyboard shortcut for this functionality is Ctrl-L or Ctrl-T. Multiple table can exist on the same worksheet.

Once your data is marked as a table, the table will dynamically expand when new data is added.

Each table is automatically given a name, starting with Table1. The name can be change via the Table tools - Design tab.

In formulas each table can be referenced by it's name.

=SUM(Table1)

Each column heading in the table is also usable in formulas.

=SUM(Table1[Column1])

---

In versions of Excel prior to 2007, 'Dynamic named ranges' can be used.

These can be created via the Insert - Name - Define menu.

Give the 'Dynamic named range' a name (e.g. Table1) and enter a formula similar to the following assuming your matirx starts in cell A1:

=OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!A:A)-1,3)

If your matrix starts in cell D10 the formula would look like this

=OFFSET(Sheet1!$D$10,1,0,COUNTA(Sheet1!D:D)-1,3)

This formula excludes any column heading in the matrix. It selects data on a 'n x 3' basis.

In formulas each 'Dynamic named range' can be referenced by it's name.

=SUM(Table1)

You will need to review the layout of your worksheet as the dynamic named range works out it's number of rows by counting all items that appear in the first column of data.
If you have cells populated above and/or below your matrix they will be included in the calculation and the 'Dynamic named range' will include rows below your data matrix.

To see which cells are included in a 'Dynamic named range' or table press F5 and type in it's name, then click on OK.