Software

Let Excel's Indirect function keep data at your fingertips

Displaying key data on a sheet at the beginning of a workbook makes it easy to get a quick snapshot of essential information. See how simple it is to put this handy trick to work.
Rather than have your users search through dozens of worksheets for specific data, put the data they need most in its own worksheet in the front of the workbook. For example, say you have the worksheet shown in Figure A, which keeps track of replacement costs in your Inventory workbook.

Figure A

sample worksheet

Follow these steps:

  1. Click on E8.
  2. Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
  3. Enter Total_cost in the Name text box and then click OK.
  4. Click on E9.
  5. Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
  6. Click on E10.
  7. Enter Most_Expensive in the Name text box and then click OK.
  8. Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
  9. Enter Least_Expensive in the Name text box (Figure B) and click OK.

    Figure B

    worksheet setup

    1. Create a new blank sheet, move it to the front of the workbook, and enter the data shown in Figure C.

    Figure C

    worksheet data

    Now, when the user opens the workbook, Excel displays the data shown in Figure D for replacement costs on the first sheet of the workbook.

    Figure D

    indirect data


    Miss an Excel tip?

    Check out the Microsoft Excel archive and catch up on other Excel tips.

    Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

    Editor's Picks