Excel: SELECT() Function Interprets Relative Names Differently

ID: Q77800

The information in this article applies to:

Microsoft Excel for Windows, versions 2.1, 3.0, 4.0
Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.0, 4.0
Microsoft Excel for OS/2, versions 2.2 and 3.0

SUMMARY

In Microsoft Excel, if you define the name "boo" on the active worksheet as an absolute reference, the following macro command selects the cell that is defined as "boo" on the active worksheet:


   =SELECT(!boo)

However, if you define "boo" as a relative reference, Excel selects relative to the position of the SELECT() statement in the macro sheet rather than relative to the active cell. In other words, if the SELECT(!boo) command is in cell A2 of the macro sheet, the cell that is selected is always relative to cell A2 on the active worksheet.

This behavior is part of the SELECT() statement's design, but it may return unexpected results in some cases.

MORE INFORMATION

The following macro statement correctly selects the cell relative to the active cell on the active worksheet:


   =SELECT("boo")

The exclamation mark is not necessary because the quotation marks cause Excel to assume that "boo" is defined on the active worksheet.

This behavior occurs if you do not include quotation marks because Excel interprets the position of "boo" and passes the resulting reference to the SELECT() command. If "boo" is interpreted, Excel references the cell containing the SELECT() command.

If you enclose "boo" in quotation marks, Excel passes the name itself to the SELECT() command for interpretation, rather the resulting reference. This allows the SELECT() command to interpret the location based on the active cell on the worksheet.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, pages 378-381

"Microsoft Excel Function Reference," version 3.0, pages 209-210

Additional query words: 1.5 1.50 2.1 2.2 2.20 3.0

Keywords :
Version :
Platform :
Issue type :