Let Excel's Indirect function keep data at your fingertips
- Date: December 2nd, 2008
- Author: Mary Ann Richardson
- Category: Microsoft Excel, Tips
- Tags: Microsoft Word 2007, Microsoft Excel, Microsoft Office, Microsoft Word, Word Processors, Office Suites, Software, Mary Ann Richardson
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
Follow these steps:
- Click on E8.
- Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
- Enter Total_cost in the Name text box and then click OK.
- Click on E9.
- Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
- Click on E10.
- Enter Most_Expensive in the Name text box and then click OK.
- Go to Insert | Name and click Define. (In Word 2007, select Define Name in the Defined Names group on the Formulas tab.)
- Enter Least_Expensive in the Name text box (Figure B) and click OK.
Figure B
- Create a new blank sheet, move it to the front of the workbook, and enter the data shown in Figure C.
Figure C
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
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.
