XL2001: Range Finder Behaves Differently Than in Excel 98 (291568)


The information in this article applies to:

  • Microsoft Excel 2001 for Mac
This article was previously published under Q291568

SYMPTOMS

If you click in the formula bar, range designations and parentheses are not color-coded to match the colors of the Range Finder borders in the worksheet, unlike the behavior of Microsoft Excel 98 Macintosh edition.

Likewise, if you drag the color-coded Range Finder borders in the worksheet to change the range selection, you may change the formula to an invalid range and receive a #NAME? error.

CAUSE

This problem is caused by a change in functionality in Microsoft Excel 2001 for Mac.

In Excel 2001, Microsoft removed the Range Finder functionality from the formula bar. However, when you click in the formula bar, the Range Finder border still appears, and you can drag the Range Finder selection. But doing so may cause incorrect formula range notation and errors.

WORKAROUND

You can still use the Range Finder in Excel 2001 to change the ranges in cell formulas, but you must edit the formula in the cell instead of the formula bar. To do this, follow these steps:
  1. On the Edit menu, click Preferences.
  2. On the Edit tab, click to select the Edit directly in cell check box.
  3. Click OK.
  4. Double-click the cell in which you want to edit the formula (do not click the formula bar).

    The cell range in the cell formula is colored to match the Range Finder border.
  5. Place the mouse pointer over the lower-right corner of the Range Finder.
  6. When the pointer shape changes to a rectangle with triangles in the upper-left and lower-right corners, click the mouse and drag the color-coded Range Finder border to select the new range.

    The range in the formula changes to match the Range Finder selection, regardless of where the insertion point is in the formula.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Excel X for Mac.

MORE INFORMATION

Excel does not use colors for parentheses in formulas in either the formula bar or when you edit a formula in a cell.

Excel uses colors for ranges in formulas only when you edit a formula in a cell. To edit formulas in a cell, the Edit directly in cell check box must be selected.

REFERENCES

For more information about how to use Range Finder, click the Office Assistant, type locate or change the cells a formula refers to by using range finder, click Search, and then click a topic to view it.

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar.
Modification Type:MajorLast Reviewed:9/11/2002
Keywords:kbbug KB291568
©2002 Microsoft Corporation. All rights reserved.