| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
strange conditional format behavior
Hi Execel Guru's,
I have created a Worksheet that shows if laboratory results are available in my database. To do so there are 3 components: 1. the year plan (in column A and B; starting from row 11 with 2007 jan 1 in A11; and a samplecode in B11) the samplecode is blank or A,B,C,D,E,F,G. 2. a lookuptable with row 1 left blank, headerrow=2 with labcode in column F--DO and the 7 samplecodes in E3:E9 and markers ("X"-ses) at the intersection of the samplecodes (rows) and labcodes (columns) 3. the data retreved from the database. starting as E11 with the date and the values for the labcode in the corresponding column with the lookuptable. 4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give ther rownumber of the corresponding samplecode in the lookuptable for that day. 5. formulacolum (D) with formula: =COUNTA(F11:DO11) in D11 to count the number of results for that day as well as =COUNTA(F3:DO3) in front of the lookuptable to count te expected number of results D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-- green, and =INDIRECT("R"&C11&"C"&COLOMN();0)<D11--red witch indicates if te results meet the plan. So far all go's well. Now I want for each labcode in a row to do the same, witch is indicating green if th result meets the plan, and red if there is no value. To do so seemes easy, use the next formula in cell F11: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "",AND _ (INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBL ANK(F11)))) if I put this formula in a cell it returns true or false as expected, but when put in the formula aerea from CF, nothing happens. I also tested the two parts within the "AND" section separate, they work well. I als substituted one of them with true, again no result. I do'nt understand this animore, anyone of you??? I hope there is an answere or explanation, and the problem is clear enough. regards, Ernst Schuurman |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
strange conditional format behavior
It's hard for the average user to understand this, but...
The problem is that this expression returns an array: COLUMN(F:F) Even though the array is a 1 element array, it's still an array. I can't explain why it works as a sheet function (might be related to the implicit intersection rule) but it won't work as is with CF. You don't need this: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "", Try one of these: =AND(INDIRECT("R"&$C11&"C"&INDEX(COLUMN(F:F),1),0) ="X",NOT(ISBLANK(F11))) =AND(INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0) ="X",NOT(ISBLANK(F11))) I'm not sure why you're using this: NOT(ISBLANK(F11)) If as you say, F11 contains a COUNTA formula then NOT(ISBLANK(F11)) will *always* be TRUE. So, you should be able to use: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X" -- Biff Microsoft Excel MVP "Ernst Schuurman" wrote in message ... Hi Execel Guru's, I have created a Worksheet that shows if laboratory results are available in my database. To do so there are 3 components: 1. the year plan (in column A and B; starting from row 11 with 2007 jan 1 in A11; and a samplecode in B11) the samplecode is blank or A,B,C,D,E,F,G. 2. a lookuptable with row 1 left blank, headerrow=2 with labcode in column F--DO and the 7 samplecodes in E3:E9 and markers ("X"-ses) at the intersection of the samplecodes (rows) and labcodes (columns) 3. the data retreved from the database. starting as E11 with the date and the values for the labcode in the corresponding column with the lookuptable. 4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give ther rownumber of the corresponding samplecode in the lookuptable for that day. 5. formulacolum (D) with formula: =COUNTA(F11:DO11) in D11 to count the number of results for that day as well as =COUNTA(F3:DO3) in front of the lookuptable to count te expected number of results D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-- green, and =INDIRECT("R"&C11&"C"&COLOMN();0)<D11--red witch indicates if te results meet the plan. So far all go's well. Now I want for each labcode in a row to do the same, witch is indicating green if th result meets the plan, and red if there is no value. To do so seemes easy, use the next formula in cell F11: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "",AND _ (INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBL ANK(F11)))) if I put this formula in a cell it returns true or false as expected, but when put in the formula aerea from CF, nothing happens. I also tested the two parts within the "AND" section separate, they work well. I als substituted one of them with true, again no result. I do'nt understand this animore, anyone of you??? I hope there is an answere or explanation, and the problem is clear enough. regards, Ernst Schuurman |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
strange conditional format behavior
So, you should be able to use:
=INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X " Ooops! I copied the above formula from the longer version and didn't "clean" it correctly. Should be: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),0)="X" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... It's hard for the average user to understand this, but... The problem is that this expression returns an array: COLUMN(F:F) Even though the array is a 1 element array, it's still an array. I can't explain why it works as a sheet function (might be related to the implicit intersection rule) but it won't work as is with CF. You don't need this: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "", Try one of these: =AND(INDIRECT("R"&$C11&"C"&INDEX(COLUMN(F:F),1),0) ="X",NOT(ISBLANK(F11))) =AND(INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0) ="X",NOT(ISBLANK(F11))) I'm not sure why you're using this: NOT(ISBLANK(F11)) If as you say, F11 contains a COUNTA formula then NOT(ISBLANK(F11)) will *always* be TRUE. So, you should be able to use: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X" -- Biff Microsoft Excel MVP "Ernst Schuurman" wrote in message ... Hi Execel Guru's, I have created a Worksheet that shows if laboratory results are available in my database. To do so there are 3 components: 1. the year plan (in column A and B; starting from row 11 with 2007 jan 1 in A11; and a samplecode in B11) the samplecode is blank or A,B,C,D,E,F,G. 2. a lookuptable with row 1 left blank, headerrow=2 with labcode in column F--DO and the 7 samplecodes in E3:E9 and markers ("X"-ses) at the intersection of the samplecodes (rows) and labcodes (columns) 3. the data retreved from the database. starting as E11 with the date and the values for the labcode in the corresponding column with the lookuptable. 4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give ther rownumber of the corresponding samplecode in the lookuptable for that day. 5. formulacolum (D) with formula: =COUNTA(F11:DO11) in D11 to count the number of results for that day as well as =COUNTA(F3:DO3) in front of the lookuptable to count te expected number of results D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-- green, and =INDIRECT("R"&C11&"C"&COLOMN();0)<D11--red witch indicates if te results meet the plan. So far all go's well. Now I want for each labcode in a row to do the same, witch is indicating green if th result meets the plan, and red if there is no value. To do so seemes easy, use the next formula in cell F11: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "",AND _ (INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBL ANK(F11)))) if I put this formula in a cell it returns true or false as expected, but when put in the formula aerea from CF, nothing happens. I also tested the two parts within the "AND" section separate, they work well. I als substituted one of them with true, again no result. I do'nt understand this animore, anyone of you??? I hope there is an answere or explanation, and the problem is clear enough. regards, Ernst Schuurman |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
strange conditional format behavior
Hi Biff,
Thanks for your answer. You are absolutely wright about the column stuff, I even do'nt need it at all, because =INDIRECT("R"&$C11&"C,0)="X" will do exactly what I need. About the NOT(ISBLANK(F11)) part: Cell F11 is where the part with the imported results starts. The lookup table tells me that for that day (2007 jan 1 for row 11) if the samplecode that day has an "X" in column F if that is true and F11 is not empty the cell colors green, But when there is no value in F11 the cell colors red to indicate that a result is missing. I just changed the formula in the CF and it worked great. So this version works as I need: =IF(ISBLANK(INDIRECT("R"&$C11&"C,0)),"",AND(INDIRE CT("R"&$C11&"C,0)="X",NOT(ISBLANK(F11)))) I do'nt know why I didn't see for myself that I could do without using the absolute column in the indirect part. Thanks so much for triggering me. You are great as al the others in this forum. I forgot to tell that I read these posts for some time now and I leared alot. It even changed my style regarding worksheet devellopment. I wish you and all the others a happy 2008 and hope to read much more interesting posts from and with you. regards, Ernst "T. Valko" schreef in bericht ... So, you should be able to use: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)=" X" Ooops! I copied the above formula from the longer version and didn't "clean" it correctly. Should be: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),0)="X" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... It's hard for the average user to understand this, but... The problem is that this expression returns an array: COLUMN(F:F) Even though the array is a 1 element array, it's still an array. I can't explain why it works as a sheet function (might be related to the implicit intersection rule) but it won't work as is with CF. You don't need this: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "", Try one of these: =AND(INDIRECT("R"&$C11&"C"&INDEX(COLUMN(F:F),1),0) ="X",NOT(ISBLANK(F11))) =AND(INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0) ="X",NOT(ISBLANK(F11))) I'm not sure why you're using this: NOT(ISBLANK(F11)) If as you say, F11 contains a COUNTA formula then NOT(ISBLANK(F11)) will *always* be TRUE. So, you should be able to use: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X" -- Biff Microsoft Excel MVP "Ernst Schuurman" wrote in message ... Hi Execel Guru's, I have created a Worksheet that shows if laboratory results are available in my database. To do so there are 3 components: 1. the year plan (in column A and B; starting from row 11 with 2007 jan 1 in A11; and a samplecode in B11) the samplecode is blank or A,B,C,D,E,F,G. 2. a lookuptable with row 1 left blank, headerrow=2 with labcode in column F--DO and the 7 samplecodes in E3:E9 and markers ("X"-ses) at the intersection of the samplecodes (rows) and labcodes (columns) 3. the data retreved from the database. starting as E11 with the date and the values for the labcode in the corresponding column with the lookuptable. 4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give ther rownumber of the corresponding samplecode in the lookuptable for that day. 5. formulacolum (D) with formula: =COUNTA(F11:DO11) in D11 to count the number of results for that day as well as =COUNTA(F3:DO3) in front of the lookuptable to count te expected number of results D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-- green, and =INDIRECT("R"&C11&"C"&COLOMN();0)<D11--red witch indicates if te results meet the plan. So far all go's well. Now I want for each labcode in a row to do the same, witch is indicating green if th result meets the plan, and red if there is no value. To do so seemes easy, use the next formula in cell F11: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "",AND _ (INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBL ANK(F11)))) if I put this formula in a cell it returns true or false as expected, but when put in the formula aerea from CF, nothing happens. I also tested the two parts within the "AND" section separate, they work well. I als substituted one of them with true, again no result. I do'nt understand this animore, anyone of you??? I hope there is an answere or explanation, and the problem is clear enough. regards, Ernst Schuurman |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
strange conditional format behavior
Glad you got it worked out. Thanks for feeding back!
I just changed the formula in the CF and it worked great. So this version works as I need: =IF(ISBLANK(INDIRECT("R"&$C11&"C,0)),"",AND(INDIR ECT("R"&$C11&"C,0)="X",NOT(ISBLANK(F11)))) If the formula above is what you're using for the CF, it will work but you don't need the IF: =IF(ISBLANK(INDIRECT("R"&$C11&"C,0)),"", This is all you need: =AND(INDIRECT("R"&$C11&"C,0)="X",NOT(ISBLANK(F11)) ) You can even reduce it to: =AND(INDIRECT("R"&$C11&"C,0)="X",F11<"") -- Biff Microsoft Excel MVP "Ernst Schuurman" wrote in message ... Hi Biff, Thanks for your answer. You are absolutely wright about the column stuff, I even do'nt need it at all, because =INDIRECT("R"&$C11&"C,0)="X" will do exactly what I need. About the NOT(ISBLANK(F11)) part: Cell F11 is where the part with the imported results starts. The lookup table tells me that for that day (2007 jan 1 for row 11) if the samplecode that day has an "X" in column F if that is true and F11 is not empty the cell colors green, But when there is no value in F11 the cell colors red to indicate that a result is missing. I just changed the formula in the CF and it worked great. So this version works as I need: =IF(ISBLANK(INDIRECT("R"&$C11&"C,0)),"",AND(INDIRE CT("R"&$C11&"C,0)="X",NOT(ISBLANK(F11)))) I do'nt know why I didn't see for myself that I could do without using the absolute column in the indirect part. Thanks so much for triggering me. You are great as al the others in this forum. I forgot to tell that I read these posts for some time now and I leared alot. It even changed my style regarding worksheet devellopment. I wish you and all the others a happy 2008 and hope to read much more interesting posts from and with you. regards, Ernst "T. Valko" schreef in bericht ... So, you should be able to use: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)= "X" Ooops! I copied the above formula from the longer version and didn't "clean" it correctly. Should be: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),0)="X" -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... It's hard for the average user to understand this, but... The problem is that this expression returns an array: COLUMN(F:F) Even though the array is a 1 element array, it's still an array. I can't explain why it works as a sheet function (might be related to the implicit intersection rule) but it won't work as is with CF. You don't need this: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "", Try one of these: =AND(INDIRECT("R"&$C11&"C"&INDEX(COLUMN(F:F),1),0) ="X",NOT(ISBLANK(F11))) =AND(INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0) ="X",NOT(ISBLANK(F11))) I'm not sure why you're using this: NOT(ISBLANK(F11)) If as you say, F11 contains a COUNTA formula then NOT(ISBLANK(F11)) will *always* be TRUE. So, you should be able to use: =INDIRECT("R"&$C11&"C"&COLUMNS($A11:F11),1),0)="X" -- Biff Microsoft Excel MVP "Ernst Schuurman" wrote in message ... Hi Execel Guru's, I have created a Worksheet that shows if laboratory results are available in my database. To do so there are 3 components: 1. the year plan (in column A and B; starting from row 11 with 2007 jan 1 in A11; and a samplecode in B11) the samplecode is blank or A,B,C,D,E,F,G. 2. a lookuptable with row 1 left blank, headerrow=2 with labcode in column F--DO and the 7 samplecodes in E3:E9 and markers ("X"-ses) at the intersection of the samplecodes (rows) and labcodes (columns) 3. the data retreved from the database. starting as E11 with the date and the values for the labcode in the corresponding column with the lookuptable. 4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give ther rownumber of the corresponding samplecode in the lookuptable for that day. 5. formulacolum (D) with formula: =COUNTA(F11:DO11) in D11 to count the number of results for that day as well as =COUNTA(F3:DO3) in front of the lookuptable to count te expected number of results D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-- green, and =INDIRECT("R"&C11&"C"&COLOMN();0)<D11--red witch indicates if te results meet the plan. So far all go's well. Now I want for each labcode in a row to do the same, witch is indicating green if th result meets the plan, and red if there is no value. To do so seemes easy, use the next formula in cell F11: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "",AND _ (INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBL ANK(F11)))) if I put this formula in a cell it returns true or false as expected, but when put in the formula aerea from CF, nothing happens. I also tested the two parts within the "AND" section separate, they work well. I als substituted one of them with true, again no result. I do'nt understand this animore, anyone of you??? I hope there is an answere or explanation, and the problem is clear enough. regards, Ernst Schuurman |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Conditional formatting, range names, implicit intersects and logicaloperations
I have had a very similar problem. I believe this to be due to the fact that intersects are not supported in Excel conditional formatting.
If you use a range name for say a row in a normal Excel formula, then it uses the value for the current column by default. Thus it applies an implicit intersection operation. If you do the same in conditional formatting formulae, which do not support intersections, Excel does not complain like it does with explicit intersections, but it just doesn't work as you would expect when used with ANDs and ORs. Strangely, whether it works or not seem to depend on the number of ANDed or ORed conditions. In my case two ORed conditions work, three did not! Import the same formulae to Open Office, just to check you are not going mad, and the same conditional formatting formula works every time. In Excel replace the range names by absolute or relative references, and everything works. Definately a bug! Sigh! Ernst Schuurman wrote: strange conditional format behavior 27-Dec-07 Hi Execel Guru's, I have created a Worksheet that shows if laboratory results are available in my database. To do so there are 3 components: 1. the year plan (in column A and B; starting from row 11 with 2007 jan 1 in A11; and a samplecode in B11) the samplecode is blank or A,B,C,D,E,F,G. 2. a lookuptable with row 1 left blank, headerrow=2 with labcode in column F--DO and the 7 samplecodes in E3:E9 and markers ("X"-ses) at the intersection of the samplecodes (rows) and labcodes (columns) 3. the data retreved from the database. starting as E11 with the date and the values for the labcode in the corresponding column with the lookuptable. 4. formulacolum (C) with formula: =MATCH(B11,$E$1:$E$9,0) in C11 to give ther rownumber of the corresponding samplecode in the lookuptable for that day. 5. formulacolum (D) with formula: =COUNTA(F11:DO11) in D11 to count the number of results for that day as well as =COUNTA(F3:DO3) in front of the lookuptable to count te expected number of results D11 has CF with formula: =INDIRECT("R"&C11&"C"&COLOMN();0)=D11-- green, and =INDIRECT("R"&C11&"C"&COLOMN();0)<D11--red witch indicates if te results meet the plan. So far all go's well. Now I want for each labcode in a row to do the same, witch is indicating green if th result meets the plan, and red if there is no value. To do so seemes easy, use the next formula in cell F11: =IF(ISBLANK(INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)), "",AND _ (INDIRECT("R"&$C11&"C"&COLUMN(F:F),0)="X",NOT(ISBL ANK(F11)))) if I put this formula in a cell it returns true or false as expected, but when put in the formula aerea from CF, nothing happens. I also tested the two parts within the "AND" section separate, they work well. I als substituted one of them with true, again no result. I do'nt understand this animore, anyone of you??? I hope there is an answere or explanation, and the problem is clear enough. regards, Ernst Schuurman EggHeadCafe - Software Developer Portal of Choice Bing Search RSS with Silverlight 3 RIA Domain Service http://www.eggheadcafe.com/tutorials...with-silv.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Strange Cell Format Behavior | Excel Discussion (Misc queries) | |||
| Strange behavior on launching. | Setting up and Configuration of Excel | |||
| Strange behavior | Excel Discussion (Misc queries) | |||
| Strange behavior | Setting up and Configuration of Excel | |||
| Strange behavior. | Excel Discussion (Misc queries) | |||
Linear Mode
