Use Excel's DGET function to zero in on information you need
Thursday, December 04, 2008 01:53 PM
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.
Microsoft Excel
Use Excel's DGET function to let users zero in on the information they need
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.
Figure A
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)
Figure B shows how the sheet will look at this point.
Figure B
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.
Figure C
Microsoft Word
Use Word's Find and Replace feature to change text from Bold to Blue
Find and Replace is not just for finding and replacing a specific word with another word; it can also be used to change a format throughout your document. For example, say you used bold to emphasize certain words, as shown in Figure A.
Figure A
You decide that you would like to change the words in bold to regular text and their font color to blue. Follow these steps:
- Go to Edit | Replace. (In Word 2007, click Edit on the Home tab and then click Replace.)
- Click in the Find What text box and press Ctrl + B.
- Click in the Replace With text box.
- Click the More button.
- Click the Format button and then click Font (Figure B).
Figure B
- Click Regular under Font style.
- Click the Color drop-down arrow and select blue (Figure C).
Figure C
- Click OK.
- Click the Replace All button (Figure D).
As you can see in Figure E, all the previously bold text is no longer bold, but blue.
Figure D
Figure E
Note: If you just need to convert to regular text, press Ctrl + B in the Find What box and then click in the Replace With box and press Ctrl + B twice. This tells Word to replace Bold with Not Bold. In a similar fashion, you can press Ctrl + I or Ctrl + U to find and replace all occurrences of text that are italicized or underlined, respectively.
Microsoft Access
Hide Access tables to safeguard them from users
You should never let users access a table directly. When they need to add, delete, or modify data, give them a form to work through. Unfortunately, some users won't be satisfied, and they'll try to work around your data entry forms. One of the simplest ways to protect a table is to hide it, as follows:
- Right-click the object in the Database window.
- Choose Properties.
- Click the Hidden option.
Doing so removes it from the Database window list, but it’s still there. Most users won’t know to look for hidden objects, but some might. Viewing hidden objects is easy enough:
- From the Tools menu, choose Custom.
- Click the View tab.
- Check the Hidden Objects item in the Show section.
Another way to hide a table is to add the prefix Usys to the table's name. Access interprets any table with that prefix as a system object and hides it. (Access hides all system objects.) Checking the Hidden Objects item (step 3, above) won’t display hidden system tables. However, clicking System Objects in the Show section will. A few users might be perceptive enough to try that option even if they don't know what it will do.
Hiding tables won't always be enough. Consider inhibiting the Database window or even deploying Access workgroups security.
There are currently no comments for this post.