E
eivind
Hi,
I have some problems using conditional formatting in Excel 2007.
I have a matrix of values divided by a diagonal, like so:
A B C ... .. .
A - 0 0 .. .
B 3 - 1 .
C 4 2 -
.... .. .
... .
..
(Sorry for the bad ASCII-drawing, but you get the idea)
Now, I want to format the values in the lower part of the diagonal
based on an evaluation of the values on the upper part of the
diagonal.
I am trying to use the following formula in my conditional formatting:
=HLOOKUP(INDIRECT(ADDRESS(ROW();1));$A$2:$J
$9;MATCH(INDIRECT(ADDRESS(1;COLUMN()));$A$2:$A$9;0);FALSE)<alpha
By using the INDIRECT(ADDRESS(...)) construct I can apply the
formatting to the entire lower part using the same rule for all cells.
The HLOOKUP finds the column corresponding to the row name (A, B,
C, ...), and MATCH finds the correct row by looking up the name of the
column (A, B, C, ...).
This way, when you're in column A row B, we lookup the value of row A
column B, and we want to apply formatting based on weither or not the
value in this cell is below a defined 'alpha' -- which is defined in
the current worksheet.
The problem is, this is not working. When I apply the formula directly
in the cells, I get the correct TRUE/FALSE values I want to use to
control the formatting. But when I apply it in the conditional
formatting dialogs it doesn't work.
The peculiar thing is: If I replace the
MATCH(INDIRECT(ADDRESS(1;COLUMN()));$A$2:$A$9;0) statement with
MATCH("A";$A$2:$A$9;0) -- that is, I spell the given column name --
the conditional formatting works. The same goes for MATCH(A2;$A$2:$A
$9;0) -- that is, I give a certain cell reference. But, the relative
INDIRECT(ADDRESS(1;COLUMN())) statement fails. I find this very
strange as the exact same formlua evaluates to TRUE or FALSE when
applies directly inside a cell.
Any ideas as to what might cause this apparently strange problem?
Regards,
Eivind Berg Johannessen
I have some problems using conditional formatting in Excel 2007.
I have a matrix of values divided by a diagonal, like so:
A B C ... .. .
A - 0 0 .. .
B 3 - 1 .
C 4 2 -
.... .. .
... .
..
(Sorry for the bad ASCII-drawing, but you get the idea)
Now, I want to format the values in the lower part of the diagonal
based on an evaluation of the values on the upper part of the
diagonal.
I am trying to use the following formula in my conditional formatting:
=HLOOKUP(INDIRECT(ADDRESS(ROW();1));$A$2:$J
$9;MATCH(INDIRECT(ADDRESS(1;COLUMN()));$A$2:$A$9;0);FALSE)<alpha
By using the INDIRECT(ADDRESS(...)) construct I can apply the
formatting to the entire lower part using the same rule for all cells.
The HLOOKUP finds the column corresponding to the row name (A, B,
C, ...), and MATCH finds the correct row by looking up the name of the
column (A, B, C, ...).
This way, when you're in column A row B, we lookup the value of row A
column B, and we want to apply formatting based on weither or not the
value in this cell is below a defined 'alpha' -- which is defined in
the current worksheet.
The problem is, this is not working. When I apply the formula directly
in the cells, I get the correct TRUE/FALSE values I want to use to
control the formatting. But when I apply it in the conditional
formatting dialogs it doesn't work.
The peculiar thing is: If I replace the
MATCH(INDIRECT(ADDRESS(1;COLUMN()));$A$2:$A$9;0) statement with
MATCH("A";$A$2:$A$9;0) -- that is, I spell the given column name --
the conditional formatting works. The same goes for MATCH(A2;$A$2:$A
$9;0) -- that is, I give a certain cell reference. But, the relative
INDIRECT(ADDRESS(1;COLUMN())) statement fails. I find this very
strange as the exact same formlua evaluates to TRUE or FALSE when
applies directly inside a cell.
Any ideas as to what might cause this apparently strange problem?
Regards,
Eivind Berg Johannessen