You can save your users considerable time (and eyestrain) by setting up a handy formula that tells Excel to display a specific cell entry for them.
Users should not have to scan hundreds of rows and columns in an Excel worksheet to locate the information they need. Instead, use the DGET function to set up your worksheets so Excel can do the searching for them. For example, say your users are continually having to scan a worksheet showing what projects the company worked on during 2007 and the total costs incurred each day. Figure A shows part of this worksheet.
Rather than having users scan the Date column to see what was spent on a specific day in January, you can set up the worksheet so that they can simply enter the date in question and have Excel find the information for them. Follow these steps:
- Type Enter Date Here: in cell H5.
- Type Date in cell H6.
- Type Project ID in cell H9.
- Type Cost in cell H10.
- In cell I9, enter: =DGET(A1:C25,2,H6:H7)
- In cell I10, enter: =DGET(A1:C25,3,H6:H7)
Users can now enter a date in H7, and Excel will return the project ID and cost of the project for that day, as shown in Figure C.
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.