Despite The Warning, Conditional Formatting Can Do Its Job On Two Different Worksheets

Q. I loved the article “Spreadsheet Smarts” by Justin D. Stein ( JofA, Jan.00, page 53). I’m particularly interested in the Conditional Formatting function and its potential benefit in financial statement preparation. But I need to apply the function to compare values on two different worksheets. However, when I try it, I get error messages. What’s up?

A. The author replies: It’s true that, if you want to use a formula within the Conditional Formatting function to compare the values of two cells from different worksheets, you will receive the error message: “You may not use references to other worksheets or workbooks for Conditional Formatting criteria.” However, you can get around this obstacle by creating a named range for at least one of the cells you wish to compare.

For example, if you want to compare the values of two cells from separate worksheets, Sheet1!A1 and Sheet2!B1, you need to create a range name for any cell not residing on the current worksheet (Sheet1). For example, you can name Sheet2!B1 “Total,” and you can now use Total in a formula to represent Sheet2!B1.

Exhibit 2

You can adjust Conditional Formatting so it can compare two values in more than one workbook.

The simplest way to create a range name is to select a cell or a range and type the name of the range in the range drop-down window to the left of the formula bar (see exhibit 2, above). The formula bar sits just beneath the toolbars at the top of your screen and shows you the contents of the selected cell. If I select Sheet2!B1, the range drop-down window contains B1. Click in the window and type Total, then press Enter. The name of your range, Total, now appears in the window.

Now we can rewrite our original formula for the Conditional Formatting for cell Sheet1!A1 to refer to Total, rather than Sheet2!B1.

After I set the format I want, the function will accept my formula and work properly. Now you can compare two values anywhere in the workbook.


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.