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

Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.

TECHNOLOGY Q&A

How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.

QUIZ

News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.