Microsoft Office Tutorials and References

In Depth Information

You can also reference worksheet-level names with the INDIRECT function. For example, suppose you have a

number of worksheets named Region1, Region2, and so on. Each sheet contains a worksheet-level name called

TotalSales.
This formula retrieves the value from the appropriate sheet using the sheet name typed in cell A1:

=INDIRECT(A1&”!TotalSales”)

If cell A1 contains the text Region2, the formula evaluates to the following:

=Region2!TotalSales

Using arrays in named formulas

An
array
is a collection of items. You can visualize an array as a single-column vertical collection, a single-row

horizontal collection, or a multirow and multicolumn collection.

Part IV of this book discusses arrays and array formulas, but this topic is also relevant

when discussing names.

You specify an array by using curly brackets. A comma or semicolon separates each item in the array. Use a

comma to separate items arranged horizontally, and use a semicolon to separate items arranged vertically.

Use the New Name dialog box to create a formula named
MonthNames
that consists of the following formula

definition:

={“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”,”Jul”,”Aug”,”Sep”,”Oct”,”Nov”,”Dec”}

This formula defines a 12-item array of text strings, arranged horizontally.

When you type this formula, make sure that you include the brackets. Entering an array

formula into the New Name dialog box is different from entering an array formula into a

cell.

After you define the
MonthNames
formula, you can use it in a formula. However, your formula needs to specify

which array item to use. The INDEX function is perfect for this. For example, the following formula returns

Aug:

=INDEX(MonthNames,8)