Microsoft KB Archive/47492

{|
 * width="100%"|

Concatenating Cell References in Excel

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0

-

SUMMARY
A cell reference can be concatenated. Ways to use them differ, depending on whether the cell reference is on a macro sheet or a worksheet. Examples on how to use each type follow the general description below.

MORE INFORMATION
Use the text concatenation symbol & (an ampersand) to join cell references. Enclose literal strings with double quotation marks. To refer to the value in another cell or a defined name, use only the cell's reference, or the name, without the quotation marks.

For example, if A5 contains the number 3, and Name is a defined name referring to the number 16, the following is true:

  Concatenation           Refers to   -           -

&quot;B&quot;&A5                 Cell B3   &quot;Sheet1.XLS!J&quot;&Name     Cell J16 on Sheet1.XLS &quot;R&quot;&Name&&quot;C1&quot;          Cell R16C1 (or A16) &quot;R[&quot;&Name&&quot;]C1&quot;        16 cells down from where the active cell is, but in column A

Worksheet
Use the INDIRECT function to convert the resulting text string to an actual reference in a worksheet, as follows:

  =INDIRECT(&quot;B&quot;&A5)

Macro
In most cases, you can use the concatenated address just as you would any normal cell reference in a macro function, as follows:

  =SELECT(&quot;R[&quot;&Name&&quot;]C1&quot;)

However, some functions require an actual reference instead of a text string. In these cases, nest the TEXTREF function inside the other function, as follows:

  =ROW(TEXTREF(&quot;R[&quot;&Name&&quot;]C1&quot;))

Another method is to use FORMULA.GOTO in much the same manner.

  =FORMULA.GOTO(&quot;R[&quot;&Name&&quot;]C1&quot;)