Microsoft KB Archive/68518

{| = Selecting, Editing, or Extracting Values from Cells in Table =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q68518

SUMMARY
When you use tables that have headings at the top of and to the left of the data, it is useful to use the Create Names command and the intersection operator to make selections, edit data, or extract values quickly. This can be especially useful in a macro.

MORE INFORMATION
For example, suppose you have a table set up as follows:

A1: Name       A2: Age         A3: Color B1: Tom        B2:  8          B3:  Red C1: Susan      C2:  9          C3:  Blue D1: Billy      D2:  7          D4: Yellow Highlight cells A1:D4. From the Formula menu, choose Create Names. In the dialog box, select Top Row and Left Column. Choose OK. This creates names for your data, which you can refer to quickly. For instance, if you type &quot;=Tom Age&quot; (without the quotation marks) in an empty cell, the value 8 is returned. The space acts as the intersection operator and finds the value at the intersection of the names that you enter. This can be especially useful in a macro. Suppose you want to change Susan's color from blue to pink. Instead of using the Vlookup, Index, and/or Match commands to find the appropriate cell to edit, you can simply type the following commands in your macro sheet: A1: =select(!Susan !Color) A2: =formula(&quot;pink&quot;) This changes &quot;blue&quot; to &quot;pink&quot;.