Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 40 Use the Same Name for Ranges on Different Worksheets

Sometimes it would be convenient to use the
same name for data in the same place on multiple worksheets within
the same workbook. Excel requires a few extra steps to make this
work.

Usually when you name a range, the name
is at the workbook level, meaning that one name refers to a specified
range on a specified worksheet wherever it is used in the workbook.
Once the name has been used, it cannot be used again to represent a
range on another worksheet. There is a way around this, however.

Assume you have a workbook with three worksheets. These three
worksheets are simply named Sheet1, Sheet2, and Sheet3. You want to
have a named range called MyRange (this can be any legitimate name)
that will refer to the range Sheet1 A1:A10 when on Sheet1, Sheet2
A1:A10 when on Sheet2, and Sheet3 A1:A10 when on Sheet3.

To do this, activate Sheet1, select the range A1:A10, and then click
in the Name box, as you did in [Hack #39]. Type Sheet1!MyRange
and then press Enter. Do the same for Sheet2 and Sheet3, typing
Sheet2!MyRange and
Sheet3!MyRange, respectively.

Now activate any sheet and click the drop arrow on the Name box. You
should see only one occurrence of the name MyRange. Select this and
you will be taken directly to the range A1:A10. Now activate any
other sheet and do the same. You always will be taken to the range
A1:A10 of that sheet.

You can do this because you preceded the
name with the sheet name followed by ! (an
exclamation mark). If you select Insert Name
Define, you will see only one name: the one that refers to the
currently active sheet.

If your worksheet name includes
spaces, you cannot simply refer to the range Sheet1 A1:A10 as
Sheet1!MyRange.
Instead, you must call it 'Sheet
1'!MyRange, putting a single apostrophe around
the word Sheet1. In fact, you also
can use single apostrophes with a worksheet name with no spaces, so
it is a good idea to always use single apostrophes when referring to
worksheet names to cover all your bases.

You can use a relative reference named
range as well. By default, named ranges are absolute, but you do not
have to leave them this way. Try the following.

Select cell A11 on any worksheet and
then select Insert Name Define. In the Names in
Workbook: box, type MyNumbers. In the Refers
To: box, type =A$1:A$10 and then click Add,
then OK.

Now enter the number
1 in cell A1. Select cell A1, move your cursor
to the fill handle, and press the left mouse button. While holding
down the Ctrl key, drag down to cell A10. Holding down the Ctrl key
with a single number will cause Excel to create a list incremented by
1.

Enter 1 in cell B1 and drag down to cell B10,
without holding down the Ctrl key this time. Into cell A11, enter the
following formula:

=SUM(MyNumbers)

In cell B11, enter this formula:

=SUM(MyNumbers)

You should
get 55 and 10, respectively,
because cell A11 was active when you selected Insert Name
Define and referred the range name to A$1:A$10, which is a
relative column and absolute row named range.

The dollar sign ($) forces any range to be absolute.

When you use the name
MyNumbers in a formula, it always will refer
to the 10 cells immediately above the formula. If you use
=SUM(MyNumbers) in cell A11 of another worksheet,
it still will refer to cells A1:A10 on the sheet that was active when
you originally created the range name.

Suppose you want to simplify the summing of the 10 cells mentioned
earlier. Select cell A11 on any worksheet. Select Insert
Name Define and type
MySum in the Name box.
Then, in the Refers To: box, type the following:

=SUM(A$1:A$10)

Click Add, then OK. Now enter the
number 1 in cell A1. Select cell A1, move your
cursor to the fill handle, and press the left mouse button. Hold down
the Ctrl key and drag down to cell A10. Once you've
done that, enter 1 in cell B1, and drag down
to cell B10 without holding down the Ctrl key.

In cell A11, enter the following formula:

 =MySum

In cell B11, enter this formula:

=MySum

You
will get the same results you got before, but without requiring the
SUM function. Mixing up the absolute and relative
references and nesting a few functions together can be very handy and
can save a lot of work.