Daily Dose of Excel

Conditional Formatting Across Sheets

January 30, 2006, 9:58 pm by Dick Kusleika

Created by David Hager

To make a conditional format based on the value in the previous
worksheet, create the following defined name formulas.

GlobRef as:

=INDIRECT(”rc”,FALSE)

which gives the value from the cell it is used in.

PrevShtValue as:

=INDIRECT(INDEX(GET.WORKBOOK(1),GET.DOCUMENT(87)-1)&”!”&ADDRESS(ROW(),
COLUMN()))

which gives the value from the cell of the same address in the previous
sheet.

Then, combine these in yet another defined name formula.

GTPSV (this cell value is greater than previous sheet value) as:

=GlobRef>PrevShtValue

which is used as the conditional formatting formula (in Excel 97 and later
versions).

Editor’s Note: The value on the sheet to the left, in my test, is zero (i.e., less than two).

conditional formatting dialog with effected range behind it

Category: Excel Experts E-Letter | Comment (RSS) | Trackback

Brett says:

January 31, 2006 at 11:59 am

Is there an exhaustive list of the argumants for Get.document and get.workbook? I can’t find one in Excel.
Brett says:

January 31, 2006 at 12:20 pm

that’s “argumEnts”.The 87 in GET.DOCUMENT(87) returns the sheet position in the workbook.
David Wasserman says:

January 31, 2006 at 2:25 pm

Brett,
If you have Excel 2000, you can download help on these Excel 4 Macro functions from:

http://www.microsoft.com/downloads/details.aspx?FamilyID=C09BF7F7-D30E-4CE9-8930-5D03748CA5CD&displaylang=en
Brett says:

February 01, 2006 at 10:45 am

THANKS!!
Mark Aufflick says:

July 23, 2007 at 6:17 pm

PrevShtValue needs a little tweak if your filename or tab name has a space in it:

=INDIRECT(”‘”&INDEX(GET.WORKBOOK(1),GET.DOCUMENT(87)-1)&”’!”&ADDRESS(ROW(),
COLUMN()))

Note the ‘ marks wrapping the worksheet reference.

Otherwise - awesome!

To post VBA code in your comment, use <code> tags, like this: <code lang="vb">Code goes here</code>.