Conditional Formatting Across Sheets

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

Posted in Uncategorized

5 thoughts on “Conditional Formatting Across Sheets

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

  2. 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!

Leave a Reply

Your email address will not be published. Required fields are marked *