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).
Is there an exhaustive list of the argumants for Get.document and get.workbook? I can’t find one in Excel.
that’s “argumEnts”.The 87 in GET.DOCUMENT(87) returns the sheet position in the workbook.
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
THANKS!!
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!