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)
Search JabSto ::




Custom Search