Concatenating Cell References in Excel

ID: Q47492

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
   -------------           ---------

   "B"&A5                  Cell B3
   "Sheet1.XLS!J"&Name     Cell J16 on Sheet1.XLS
   "R"&Name&"C1"           Cell R16C1 (or A16)
   "R["&Name&"]C1"         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("B"&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("R["&Name&"]C1")

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("R["&Name&"]C1"))

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


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

REFERENCES

Online Help, version 5.0, Reference Information, Microsoft Excel Macro Functions Contents

"Function Reference," version 4.0, page 174

"Function Reference," version 3.0, page 93

"Functions and Macros," version 2.x, page 287

Additional query words: concat

Keywords :
Version :
Platform :
Issue type :