branflakes
New Member
- Joined
- Sep 25, 2013
- Messages
- 2
I initially had this problem today in a large spreadsheet, but I have made a test spreadsheet that does the same thing. You really need to have the file for yourself to see exactly what is wrong.
The problem: Conditional formatting is not acting the way it should. A formula in it may not function, while the exact same formula functions perfectly in an individual cell.
The file: https://dl.dropboxusercontent.com/u/25743534/Conditional Problem.xlsx
File setup: The sheet "OPT" is just for a checkbox that I want to use to turn off conditional formatting (useful before printing since I don't want the highlights to print). Sheet1 has a data table on it and nothing else. Sheet2 through Sheet5 all have that same data table, but with two differences - A1 and B2. My goal would be to highlight those cells in each of those sheets. Sheet2 through Sheet5 also have another table, each cell containing the conditional formatting formula (just translated left 4 columns) used on that respective sheet for debugging purposes (referred to from here on out as the debug table). A value of TRUE in the debug table means that the corresponding cell in the data table should be highlighted.
Some background before I get into the details: There is a reason why some of the formulas seem to be unnecessarily complicated (notably the use of INDIRECT and ADDRESS), but I assure you that these are necessary in the actual file. This file is much simpler but I still wanted to include those functions in case they were at fault here. I'm also not allowed to use macros for anything.
Explanation: I am trying to use conditional formatting to highlight the differences between two tables on two different sheets. I also want to have the ability to turn all conditional formatting off, using the checkbox on the first sheet.
Here is the original formula (used on sheet2):
This one works. So the logical thing to do to make the checkbox work is to add an AND, and check the cell linked with the checkbox. That cell is named "OPT" workbook-wide. That formula is on sheet 3.
Here is the formula on sheet3, the one that I would like to ultimately use:
As you can see, I have added the AND. Now the conditional formatting is not highlighting the cells. However, the debug table is correct. Go ahead and uncheck the checkbox in the sheet named "OPT". The debug table should then be all FALSE. Everything seems to be working correctly, except the conditional formatting. Make sure you recheck the checkbox before checking the next sheet.
Here is the formula on sheet4, the same as sheet3, except using OR:
Now since the value of OPT is true, OR should always return TRUE. The debug table shows that is correct. But conditional formatting still isn't highlighting anything.
The formula used on sheet5 is there just to show what I'm trying to do works without the use of INDIRECT or ADDRESS:
I apologize for the length of this post - the problem is hard to explain well since it's very odd. The behavior only happens when the formula is used in conditional formatting.
Thanks.
The problem: Conditional formatting is not acting the way it should. A formula in it may not function, while the exact same formula functions perfectly in an individual cell.
The file: https://dl.dropboxusercontent.com/u/25743534/Conditional Problem.xlsx
File setup: The sheet "OPT" is just for a checkbox that I want to use to turn off conditional formatting (useful before printing since I don't want the highlights to print). Sheet1 has a data table on it and nothing else. Sheet2 through Sheet5 all have that same data table, but with two differences - A1 and B2. My goal would be to highlight those cells in each of those sheets. Sheet2 through Sheet5 also have another table, each cell containing the conditional formatting formula (just translated left 4 columns) used on that respective sheet for debugging purposes (referred to from here on out as the debug table). A value of TRUE in the debug table means that the corresponding cell in the data table should be highlighted.
Some background before I get into the details: There is a reason why some of the formulas seem to be unnecessarily complicated (notably the use of INDIRECT and ADDRESS), but I assure you that these are necessary in the actual file. This file is much simpler but I still wanted to include those functions in case they were at fault here. I'm also not allowed to use macros for anything.
Explanation: I am trying to use conditional formatting to highlight the differences between two tables on two different sheets. I also want to have the ability to turn all conditional formatting off, using the checkbox on the first sheet.
Here is the original formula (used on sheet2):
Code:
=(A1<>INDIRECT("'Sheet1'!"&ADDRESS(ROW(),COLUMN())))Here is the formula on sheet3, the one that I would like to ultimately use:
Code:
=AND(OPT,(A1<>INDIRECT("'Sheet1'!"&ADDRESS(ROW(),COLUMN()))))Here is the formula on sheet4, the same as sheet3, except using OR:
Code:
=OR(OPT,(A1<>INDIRECT("'Sheet1'!"&ADDRESS(ROW(),COLUMN()))))The formula used on sheet5 is there just to show what I'm trying to do works without the use of INDIRECT or ADDRESS:
Code:
=AND(OPT,(A1<>Sheet1!A1))[CODE]
This works perfectly, except I can't do this in the actual file. I have to do some on-the-fly string manipulation in picking the sheet to have each sheet compare against the sheet before it. This sheet shows that the problem lies with the use of INDIRECT within an AND or OR. I'm very confused about why this is - can anyone elaborate on why this happens and if it's intended?
If you're curious, here is what the actual formula I'm trying to use looks like: [CODE]=AND(OPT,(A1<>INDIRECT("'Sheet"&CHAR(CODE(RIGHT(CELL("FILENAME",$A$1),1))-1)&"'!"&ADDRESS(ROW(),COLUMN()))))I apologize for the length of this post - the problem is hard to explain well since it's very odd. The behavior only happens when the formula is used in conditional formatting.
Thanks.
