ShareThis
|
TECHNOLOGY Q&A
A complicated inheritance
By J. Carlton Collins, CPA
April 2013

Q: I inherited an Excel workbook containing lengthy formulas, and I am having trouble determining exactly how these formulas work. Is there a magic trick for figuring out how complicated formulas work?

A: Excel provides a couple of tools that might help. The first tool is Evaluate Formula. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Evaluate Formula. This action will display the formula in the Evaluate Formula dialog box (pictured below).

With each click of the Evaluate button, Excel solves the next step of the formula to help you follow and understand the logic.

A second tool that might help reveal a formula’s logic is Trace Precedents. To use this tool, highlight the complicated formula, then from the Formulas tab’s Formula Auditing group, select Trace Precedents. This action inserts arrows depicting the flow of data from its source to the formula.

Click the Trace Precedents tool a second time to insert additional arrows depicting the flow of data to the second level of data (preceding the initial first level of source data). If desired, continue clicking Trace Precedents until all data flowing to the complicated formula is mapped to the original values entered in the workbook. This arrow mapping might help you better follow the flow of data through the workbook. (Note: You must place your cursor on a single cell for Trace Precedents to work. It won’t work if you select multiple cells. This means you must move your cursor to each cell where you would like to trace the data flow.)

For example, examining the flow of data in the screenshot pictured below reveals that the total formulas on rows 59 and 61 do not line up with the same columns containing the data (i.e., the January totals are contained in the February column, and so on). This might explain why the reader was having trouble making sense of these formulas.

TECHNOLOGY Q&A
Horsing around with paint
By J. Carlton Collins, CPA
April 2013

Q: We have a company logo that I want to display on a proposal’s cover atop a graphic image, but I want to get rid of the logo’s surrounding white space. Is there a quick way to accomplish this without buying fancy software or paying a graphic artist to do this for us?

A: You can achieve the effect you want using the free Paint program included with Windows, as follows:

1. Launch Paint in Windows 7, Vista, or XP by clicking the Start button and selecting All Programs, Accessories, Paint (or click on the icon if you have it on your Windows taskbar or Start menu). In Windows 8, press Windows key+F to launch the Search tool, search for MSPaint, and double-click the MSPaint icon.

2. From Paint’s Home tab, select Select from the Image group, and place a checkmark next to Transparent selection (as shown below).

3. Open your logo image in Paint, and from the Home tab, select Select, Rectangular selection, drag a selection box around your company logo, and then, from the Home tab, select Copy. (This action copies the logo only; the white area of your logo is treated as transparent, so when you paste the logo, the underlying image shows through.)

4. Open the graphic image (the image of horses in the example below) in Paint, and from the Home tab select Paste, Paste. For comparison purposes, the images below depict the pasted logo both with and without the surrounding white space.

5. Important: Once pasted, the logo image will temporarily include grab boxes that you can use to resize the logo image or drag the logo image to a new position. Once you click away from the image, the grab boxes will disappear, and you will no longer be able to resize or reposition the logo image atop the background image. If you lose the grab boxes before you finish your edits, press Ctrl+Z to remove the logo image, then repaste the logo image and try again.

(Note: If you find this procedure works only partially, then most likely your logo’s white space contains multiple shades of white (such as shadowing). In this case, you will need to change any off-white colored pixels to absolute white because paint’s Transparent selection setting affects only the pixels that are absolute white. To do this, use Paint’s Pencil, Fill with color, or Brushes tools to recolor any off-white pixels to absolute white, then repeat the steps described above.)

TECHNOLOGY Q&A
By J. Carlton Collins, CPA
April 2013

Q: Even though I’ve set my Outlook 2010 Junk E-mail Options to High, I still receive so many unsolicited emails it’s maddening. Do I need to purchase a junk email or spam program, or is there something else I can try to reduce my junk email?

A: I recommend that you try creating an Outlook rule to move email containing the words “unsubscribe” and “remove” to your junk email folder. By law, unsolicited commercial email must contain instructions for unsubscribing; therefore, the word “unsubscribe” or “remove” usually appears in such emails. Follow these steps to create this rule:

1. From the Outlook 2010 Home tab’s Move group, select Rules, Create Rule.

2. Click the Advanced Options button.

3. Under the section labeled Step 1: Select condition(s), place a check in the box labeled with specific words in the body.

4. Under the section labeled Step 2: Edit the rule description, click the underlined text specific words, then add the word unsubscribe to the Search Text using the Add button. Click OK, Next. (Repeat this step to also add the word remove to the Search Text.)

5. Under the section labeled Step 1: Select action(s), check the box labeled move it to the specified folder.

6. Under the section labeled Step 2: Edit the rule description, click the underlined text specified, then browse to and select your Junk E-mail folder. Click OK, Next.

7. Optional: If you receive any regular emails containing the word “unsubscribe” that you don’t want moved to your Junk E-mail folder (such as an industry newsletter), then under the section labeled Step 1: Select exception(s) place a check in the box labeled except with specific words in the sender’s address. Next, under the section labeled Step 2: Edit the rule description, click the underlined text specific words, and add phrases found in the approved sender’s email address to the Search list (such as AICPA). Click OK, Next. (This example exception ensures that emails from the AICPA are not moved to your junk folder.)

8. Finally, enter a rule name in the Step 1: Specify a name for this rule box, make sure the box labeled Turn on this rule is checked, and then click Finish.

Thereafter, any email you receive containing the word “unsubscribe” or “remove” will be automatically moved to your Junk E-mail folder. If you later discover emails in your Junk E-mail folder that you did not want moved, edit this rule to include additional exceptions.

TECHNOLOGY Q&A
Can I Drop(box) QuickBooks?
By J. Carlton Collins, CPA
April 2013

Q: We use a multiuser version of QuickBooks, and our remote satellite office personnel log in to my computer remotely using LogMeIn to access the system—which means that only one of us can access QuickBooks at the same time. This also means that when others access QuickBooks in this manner, I can’t use my computer to perform other non-QuickBooks-related work until they are done. To resolve this issue, I am wondering if I save our QuickBooks data file in a Dropbox folder and then share that Dropbox folder with the remote satellite office, can all of us then access the QuickBooks data file at the same time?

A: Nice idea, but sharing your QuickBooks data file via Dropbox will not work because the type of technology used by Dropbox would cause technical problems. Dropbox synchronizes copies of your cloud-based files to all of your computers that access Dropbox, so when you open a file from your Dropbox, you are really opening a copy of that file from your local computer, and Dropbox automatically synchronizes any changes you make to the cloud-based master file. This “file-syncing approach” employed by Dropbox provides two benefits, as follows: (1) Because Dropbox files open locally and not across the internet, they open much faster; and (2) this approach allows users to open their Dropbox files even when they don’t have internet access.

If you were to attempt the solution you describe to access a QuickBooks data file simultaneously, both you and the satellite office personnel would in effect be opening and changing separate synchronized copies of your QuickBooks file from your respective local computers. Thereafter, when Dropbox attempts to synchronize those local file changes with the cloud-based master file, it would encounter syncing problems and, as a result, would create multiple QuickBooks data files—one bearing the original file name and others bearing the original file name followed by the phrase “Conflicted Copy.” (Theoretically, you could share your QuickBooks data file via Dropbox as long as you do not access the QuickBooks data file at the same time, but this approach does not solve your simultaneous access problem.)

It is possible to host your QuickBooks data file in the cloud using other hosting solutions that do not employ Dropbox’s type of file-syncing technology. Intuit offers two hosting programs that enable you to host your QuickBooks data file commercially or to self-host. For more information, refer to Intuit’s “QuickBooks Hosting Program FAQ” article at tinyurl.com/a8j89ky, or search for QuickBooks hosting to find a provider in your area.

TECHNOLOGY Q&A
Five Dropbox tips
By J. Carlton Collins, CPA
April 2013

1. Receive emailed files from others directly into your Dropbox. Paid Dropbox members can create a File Dropbox, which is a special Dropbox folder that enables anyone you invite to email files directly to your Dropbox. To set up a File Dropbox, right-click on any Dropbox folder and select Create File Dropbox, enter the email addresses (separated by semicolons) of the people who will be emailing files to your Dropbox, and then click the Send Now button. (Optional: If desired, you can protect the File Dropbox with a password or set the File Dropbox to expire after a specified number of days.) The File Dropbox folders option is not available to free Dropbox subscribers.

2. Track version histories. For free subscribers, Dropbox keeps 30 days of version history for all files saved to Dropbox and longer version histories for paid subscribers. To use this feature, right-click on any file in your Dropbox and, from the pop-up menu, select Dropbox, View previous versions. This will display a list of file versions; select one and click the Restore button to open the previous version.

3. Troubleshooting the red X. If the Dropbox icon in your System Tray displays a red “x” (as shown below) either your hard drive connection is inconsistent, your computer’s system time is set incorrectly, or you have exceeded your allotted storage quota. To resolve these issues, Dropbox recommends that you keep your Dropbox on the same drive as your operating system, adjust your computer’s clock, delete unnecessary files on your Dropbox to free up space, or purchase more storage capacity.

4. Integrating Office and Dropbox. CloudOn (cloudon.com) is a free app that works with Dropbox and allows you to create, review, and edit Microsoft Word, Excel, and PowerPoint documents saved on Dropbox, Box, or Google Drive accounts from your Apple or Android tablets and smartphones.

5. Attaching Dropbox files to email. Attachments.Me (attachments.me) is a free app that links Gmail with Dropbox, Box, or Google Drive accounts, enabling you to attach files in your Dropbox to emails without having to first load the file(s) onto your smartphone or tablet.