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

Building client loyalty with payroll services

In this report, CPA experts detail their tactics for performing successful payroll services, how to mitigate risk in the process, and the impact payroll can have as a value-added service.

PODCAST

Using drones to enhance audits

Hermann Sidhu, CPA, global assurance digital leader at EY, walks us through EY’s exciting new project to use drones to help audit large warehouses and outdoor inventories.