Create a Dynamic Range
(Advanced)
You can use a dynamic
formula to define a range. As new items are added, the range will
automatically expand.
- Select the range of
cells.
- From the
Insert menu select Name4Define.
- Type a name for the
range, e.g. StaffList
- In the Refers To box, enter
an Offset formula that defines the range size, based on the number of
items in the column.
For e.g.:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$B:$B),1)
In this example, the list is on Sheet1,
starting in cell B2
The arguments used in this Offset function are:
- Reference cell:
Sheet1!$B$2
- Rows to offset:
0
- Columns to offset:
0
- Number of Rows:
COUNTA(Sheet1!$B:$B)
- Number of Columns:
1
è:
For a dynamic number of columns, replace the
Number of columns value
with:
COUNTA(Sheet1!$1:$1)
- Click OK.
This is useful for data validation lists
or any of the Lookup formulas where a range may be changing on a regular
basis. |
IsError
Function(Intermediate)
Sometimes it is
necessary to include formulas in a worksheet that will require the sheet
to be completed by a user before they will display a correct result.
In this case, you could
have error values throughout the spreadsheet until you get all the
necessary input.
The most common
workaround to suppress #DIV/0! and other errors from is to apply the
ISERROR function in your formulas.
For eg:
=IF(ISERROR(A1/B2),"",A1/B2)
This would say if cell A1 divided by B2
caused an error, then make the value in the cell equal to nothing --
otherwise (ELSE), make it whatever A1 divided by B2 really should be. This
is a good way of handling 'can't divide by zero' and similar errors. You
can also substitute just a 0 (zero) or even a text message in the place of
the empty double-quotes.
For eg:
=IF(ISERROR(A1/B2),"Input Incomplete",A1/B2)
|