LOCATE THE LAST NUMBER IN A SPREADSHEET AND COPY IT INTO A FORMULA
A client needs to keep track of frequently changing commodity prices and capture the most current figure and eventually copy it into a formula that calculates its resell price. I’m sure Excel can perform that process, but I can’t figure it out. Can you help?

So what you want is a formula that locates the last number in a column or row. An Excel array formula can perform the search and then drop the target number in any formula you wish. Here’s how it’s done:

If the column with prices starts at cell E1, the basic formula to find the last number is:
=INDEX(E1:E65000,MAX(IF(LEN(E1:E65000)> 0,ROW(E1:E65000) ,0)),1)

The 65,000 in the formula represents the maximum number of cells in a column in Excel 2003, which assumes you want to reserve that much space (from E1 to E65000 ) for the price list; if you want to reserve fewer cells in the column, just use a smaller number. If you’re using Excel 2007, the maximum is a whopping 1,048,576; in that case, substitute the E65000 with E1048576 .

Important : After entering the INDEX formula in the cell you want the number to appear in, you must then press Ctrl+Shift+Enter—a step that encloses the entire formula in curly brackets—{ }—and converts it into a powerful array formula (see screenshot below).

If you want to store the prices horizontally, that is, along the cells in a row, not down a column, and the first price is in A2, the formula looks like this:
=INDEX(A2:IV2,1,MAX(IF(LEN(A2:IV2)> 0,COLUMN(A2:IV2),0)))

Note that the number 1, which was in the other formula, is not in this formula.

To learn how to use array formulas in many more ways see “ The Power of Arrays ,” JofA , March 07, page 52, by Paul Goldwater and Timothy Fogarty.

REPLACE VISTA'S FACTORY-DESIGNATED ID CODE WITH A PERSONALIZED NAME
My firm just bought a dozen computers with the Vista operating system installed. They all have long alphanumeric codes for names, but I want to personalize each with the name of the staff person who will use the machine. How can I change the IDs?

In the pre-Vista days, renaming computers was a snap, but Vista makes you dig down several menu layers and the trip is a little tricky. Start by opening Control Panel ; if that icon isn’t already on your desktop, now’s a good time to install it because it’s one of the handiest Vista customizing tools.

To install the icon, click on Start and you’ll see a list of functions, including Control Panel (see screenshot below). Press and hold down the right mouse button on the words Control Panel and drag them onto the desktop. When you release the mouse button, a message, Create Shortcut Here , will appear. Click on it and change the name to Control Panel .

When you click the button, the Control Panel Home screen opens, and it will probably default to the Vista view. Click on System and Maintenance , bringing up another Control Panel Home screen (see screenshot below). Cursor down to System and click on it.

If the Control Panel opens instead to the Classic View , scan down the page and click on System . That will open another System and Maintenance screen (see screenshot below). Halfway down the screen locate Computer name, domain, and workgroup settings and click on Change settings .

That will open the System Properties screen (see screenshot below), and you’ve finally arrived at your target. Type in your changes at Computer description and click on Change .

One final step: You must reboot to activate the change.

MAKE DOCUMENT AND SPREADSHEET SCREENS EASIER TO READ WITH A YELLOW TINT
 Some time ago (March 2007, page 77) you published an item on ways to make computer screens of documents and spreadsheets easier to read by coloring them with a light yellow tint. I tried it, and it’s great! Thanks! Now I can easily read small, densely packed type in spreadsheets or documents, and my eyes don’t get weary. However, I have to color each new document I create. Is there some way to make Word and Excel default their screens to a color tint?

 I’m glad you find it helpful. Yes, you can default to a tinted screen. It takes a bit of effort, but, since you’re finding it so helpful, I’m sure you’ll gladly invest the effort to go through the steps. Many middle-age or older users or those who suffer from mild dyslexia or light sensitivity typically find that text on a white background—whether on a computer screen or paper— is somewhat difficult to read. The glare appears to be the culprit, making the letters and numbers seem to go out of focus or even “jump” a little.

The yellow tint default process that I’ll explain works in XP and Vista. Except for the initial steps, the method is essentially the same for both operating systems.

In XP, begin by clicking on Start , Control Panel then Display , which evokes the Display Properties screen (below left).

In the Display Properties screen, click on the Appearance tab and then on the Advanced button to open the Advanced Appearance screen (above right).

Now locate the Item box and click on the down arrow until Window is displayed and click on the arrow next to Color 1 to show a color palette (see screenshot at right). Click on Other , and a more comprehensive color palette, Color , will appear (see screenshot below).

Since there is no very light default yellow tint available in its menu of Basic colors , you must create a custom color. In the wide rainbow display on the right of the screen, move your cursor to the yellow band in the rainbow display and right-click. Then move your cursor farther to the right to the narrow band of varying shades of yellow and slide the indicator arrow up to a very light shade of yellow and click on Add to Custom Colors (the tint you selected will appear in one of the empty Custom colors boxes on the left side of the screen) and click on OK.

That’s it. You changed the default color for the pages of your applications.

And now, as you saw in the Item column in the Advanced Appearance screen, you also can customize the colors or many other displays—from title bars to message boxes. But be careful, you can accidentally create a color combination that is not easily visible—such as a black background with a dark gray message. But worry not, if you’ve changed too many colors and the combinations get out of hand, there is a safety net: You can revert everything to the original XP-set state by returning to the Display Properties screen and, under Windows and buttons , click on Windows Classic Style .

In Vista, except for the early steps, the process is similar but a little less intuitive. Click on Control Panel Home , and, under the Ease of Access group, click on the words Optimize visual display .

Then go to the bottom of the screen and click on Personalize appearance and sound effects and then on Window Color and Appearance , which finally brings up Appearance Settings. Click on Advanced and then follow the steps listed above for XP.

PUT A "LOCK" ON TRACK CHANGES

 I’ve got a “security” problem. I often distribute a memo to staff members inviting them to use Track Changes to suggest editorial changes and to make comments on it. Every now and then someone—intentionally or not, I don’t know—effectively erases all the work done by those who read and edited the memo earlier. How can I can stop that— to be sure all the comments and suggested changes are preserved?

 The best way is to password-protect the document so that only you (or a designated person) can accept or reject changes or even turn off Track Changes .

In Word 2003, open the document and click on Tools , Protect Document , and in the Editing Restrictions section of that screen, check Allow only this type of editing in the document . And in the dropdown list under Editing Restrictions , select Tracked changes . Then check Yes, Start Enforcing Protection , and you’ll be asked twice to enter a password. Save the document, and, if you then check the Tools tab, you’ll notice that Tracked changes is no longer highlighted, which means it can’t be altered or turned off for this document.

In Word 2007, access the Review tab on the Ribbon and click on Protect Document and then on Restrict Formatting and Editing (see screenshot above). Check Allow only this type of editing in the document , highlight Tracked changes , place a check next to Everyone and click on Yes , Start Enforcing Protection . Finally, after you enter passwords, click on OK and save the document.

SHORTCUTS
Windows logo key or Ctrl+Esc: Opens the Start menu from the taskbar.

Alt+Tab: Switches to the most recently used application window. Hold down Alt and press Tab more than once to move through the list. Add Shift to reverse the direction.

Alt+Esc: Switches keyboard focus to next application window on taskbar. Press Esc more than once to switch through successive windows. Add Shift to reverse the direction.

Move to next Excel worksheet: Ctrl+Page Up goes to the right; Ctrl+Page Down goes to the left.