Microsoft Office Tutorials and References
In Depth Information
Working With Range Names
by g r abbi n g on to the label - Income – in the immediately preceding cell. Had a value been stored in A5
– we l l , Exce l won ’ t dr um up a r a n g e n a me fr om the r e ; y ou’ l l ha v e to ma k e on e up y our se l f.
While we’re at it, here are some other range naming rules:
Range names can contain up to 225 characters
They must begin with a letter
You can’t define a name that resembles a cell reference, e.g., X345. X345Score is
legal, though.
T hat l ast cr i te r i on n e e ds to be r e fi n e d a bi t, an d poi n ts up a subtl e down si de whi ch be se ts n ame d
ranges. Because the pre-2007 releases of Excel were confined to 256 columns, it was permissible there
to name a range XAA321, for example, because that name refers to a cell which doesn’t exist in those
versions. That same reference won’t be accepted as a range name by Excel 2010, however – because
cell XAA321 is a perfectly valid address in 2010.
Note also that you can use the Define Names dialog box to assign a name to a value That is, you .
can enter a value in the Refers to field instead of cell coordinates, and the name you assign to that
value can be used in formulas as well (Figure A–3):
Figure A–3. Here, an actual value is assigned a name
The Scope field is a bit more obscure. Note that Workbook is set as the default scope-and that
means that this range name can be deployed in any cell in the workbook without additional
qualification. Thus if I name the range A6:A20 in Sheet 1 Income and leave the Workbook scope default
in place, I can write a formula such as
=AVERAGE(Income)
in Sheet 2 as you see it above. If, however, I define the scope of A6:A20 as Sheet1, I’d have to write the
above formula in Sheet 2 this way:
=AVERAGE(Sheet1!Income)
Obvious question, then: why bother to restrict the scope here to Sheet1? It requires more work to
write formulas this way. The answer is that you may want to name a second range, this one in Sheet2,
 
Search JabSto ::




Custom Search