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.

SPONSORED REPORT

How to make the most of a negotiation

Negotiators are made, not born. In this sponsored report, we cover strategies and tactics to help you head into 2017 ready to take on business deals, salary discussions and more.

VIDEO

Will the Affordable Care Act be repealed?

The results of the 2016 presidential election are likely to have a big impact on federal tax policy in the coming years. Eddie Adkins, CPA, a partner in the Washington National Tax Office at Grant Thornton, discusses what parts of the ACA might survive the repeal of most of the law.

QUIZ

News quiz: Scam email plagues tax professionals—again

Even as the IRS reported on success in reducing tax return identity theft in the 2016 season, the Service also warned tax professionals about yet another email phishing scam. See how much you know about recent news with this short quiz.