Selecting, Editing, or Extracting Values from Cells in Table

 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 "=Tom Age" (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("pink")
    This changes "blue" to "pink".

    REFERENCES

    "Microsoft Excel Reference." version 2.00, Page 371.

    "Microsoft Excel User's Guide." version 3.00, Page 231.

    • KBCategory: kbother
    KBSubcategory:

    Additional reference words: noupd

    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: November 2, 1994
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.