Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
Hi,
I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
Why not just use
=B2=B1 and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "SteveMax" wrote in message ... Hi, I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
Is there a way to copy & increment Conditional Format formulas? I have tried
dragging this down & it keeps the same CF formula for every cell I copy to. I was sure to not use "$" or anything...but it doesnt seem to increment. I was hoping my formula would work since I have over 400 cells to format. I tried Paste Special & got the same result. Hmm... Also...not that I think it matters much, but the idea for the CF formula would be =B2<B1 Not sure if that changes the thought. "Bob Phillips" wrote: Why not just use =B2=B1 and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "SteveMax" wrote in message ... Hi, I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
I have over 400 cells to format.
What cells do you want formatted? You don't have to list all 400 but list several. Is there a pattern? -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... Is there a way to copy & increment Conditional Format formulas? I have tried dragging this down & it keeps the same CF formula for every cell I copy to. I was sure to not use "$" or anything...but it doesnt seem to increment. I was hoping my formula would work since I have over 400 cells to format. I tried Paste Special & got the same result. Hmm... Also...not that I think it matters much, but the idea for the CF formula would be =B2<B1 Not sure if that changes the thought. "Bob Phillips" wrote: Why not just use =B2=B1 and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "SteveMax" wrote in message ... Hi, I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
the cells do have a definitive pattern which will remain static. The contents
within the cells will change dynamically(FYI, may not matter). There are 2 ways I believe which satisfy the same result/formatting I'm trying to get. Each row would need to be incremented as it goes down the column though. Formula placed in C7 =C7<C8 Formatting should render a bottom outline in C7 if TRUE. Formula placed in C7 =C7<C6 Formatting should render a top outline in C7 if TRUE. I know C6 is not in the range of cells listed below, but I think either method ends up comparing to a cell out of range at one end or the other(i.e. C51 or C6). I plan to format for blanks, so even if C7 or C50 are blank(which C6 & C51 are as well), they should still render the correct result, as I'll have the blank value CF as a precedent...or at least thats the plan. Cells for formatting a $C$7:$C$50;$H$7:$H$50;$M$7:$M$50;$R$7:$R$50;$W$7:$ W$50;$AB$7:$AB$50;$AG$7:$AG$50;$AL$7:$AL$50;$AQ$7: $AQ$50 Regards, Steve "T. Valko" wrote: I have over 400 cells to format. What cells do you want formatted? You don't have to list all 400 but list several. Is there a pattern? -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... Is there a way to copy & increment Conditional Format formulas? I have tried dragging this down & it keeps the same CF formula for every cell I copy to. I was sure to not use "$" or anything...but it doesnt seem to increment. I was hoping my formula would work since I have over 400 cells to format. I tried Paste Special & got the same result. Hmm... Also...not that I think it matters much, but the idea for the CF formula would be =B2<B1 Not sure if that changes the thought. "Bob Phillips" wrote: Why not just use =B2=B1 and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "SteveMax" wrote in message ... Hi, I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
Try this:
I don't know if you need/want to account for empty cells or not. Select the range C7:C50. C7 is the active cell. It's fill color is clear while the other selected cells have a blueish color. Goto FormatConditional Formatting Formula Is: =C8<C7 Click the Format button Select the Borders tab Select the line style and the location for the border OK out With the range C7:C50 still selected Goto EditCopy Then select your other ranges: H7:H50; M7:M50; R7:R50 etc Then Goto EditPaste SpecialFormatsOK -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... the cells do have a definitive pattern which will remain static. The contents within the cells will change dynamically(FYI, may not matter). There are 2 ways I believe which satisfy the same result/formatting I'm trying to get. Each row would need to be incremented as it goes down the column though. Formula placed in C7 =C7<C8 Formatting should render a bottom outline in C7 if TRUE. Formula placed in C7 =C7<C6 Formatting should render a top outline in C7 if TRUE. I know C6 is not in the range of cells listed below, but I think either method ends up comparing to a cell out of range at one end or the other(i.e. C51 or C6). I plan to format for blanks, so even if C7 or C50 are blank(which C6 & C51 are as well), they should still render the correct result, as I'll have the blank value CF as a precedent...or at least thats the plan. Cells for formatting a $C$7:$C$50;$H$7:$H$50;$M$7:$M$50;$R$7:$R$50;$W$7:$ W$50;$AB$7:$AB$50;$AG$7:$AG$50;$AL$7:$AL$50;$AQ$7: $AQ$50 Regards, Steve "T. Valko" wrote: I have over 400 cells to format. What cells do you want formatted? You don't have to list all 400 but list several. Is there a pattern? -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... Is there a way to copy & increment Conditional Format formulas? I have tried dragging this down & it keeps the same CF formula for every cell I copy to. I was sure to not use "$" or anything...but it doesnt seem to increment. I was hoping my formula would work since I have over 400 cells to format. I tried Paste Special & got the same result. Hmm... Also...not that I think it matters much, but the idea for the CF formula would be =B2<B1 Not sure if that changes the thought. "Bob Phillips" wrote: Why not just use =B2=B1 and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "SteveMax" wrote in message ... Hi, I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
I tried this & to my surprise it "almost" worked for me. By almost, I mean
that it formatted cells where data is equal 2 consecutives rows...but anything 2 did not format. I tried a slight variance, however, and it seems to have solved my problem. I changed the formula entered in C7 to =C7<C8(as opposed to =C8<C7) And I only entered it in C7(w/o highlighting C7:C50)...then copy&paste specialformats to every other cell. Oddly enough, I could swear I tried it that way before. I'm not necessarily convinced the variance made the difference, as I had other CF's in play as well...but it is working perfectly now & I am extremely happy with the results. Thank you for the help Biff. Regards, Steve "T. Valko" wrote: Try this: I don't know if you need/want to account for empty cells or not. Select the range C7:C50. C7 is the active cell. It's fill color is clear while the other selected cells have a blueish color. Goto FormatConditional Formatting Formula Is: =C8<C7 Click the Format button Select the Borders tab Select the line style and the location for the border OK out With the range C7:C50 still selected Goto EditCopy Then select your other ranges: H7:H50; M7:M50; R7:R50 etc Then Goto EditPaste SpecialFormatsOK -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... the cells do have a definitive pattern which will remain static. The contents within the cells will change dynamically(FYI, may not matter). There are 2 ways I believe which satisfy the same result/formatting I'm trying to get. Each row would need to be incremented as it goes down the column though. Formula placed in C7 =C7<C8 Formatting should render a bottom outline in C7 if TRUE. Formula placed in C7 =C7<C6 Formatting should render a top outline in C7 if TRUE. I know C6 is not in the range of cells listed below, but I think either method ends up comparing to a cell out of range at one end or the other(i.e. C51 or C6). I plan to format for blanks, so even if C7 or C50 are blank(which C6 & C51 are as well), they should still render the correct result, as I'll have the blank value CF as a precedent...or at least thats the plan. Cells for formatting a $C$7:$C$50;$H$7:$H$50;$M$7:$M$50;$R$7:$R$50;$W$7:$ W$50;$AB$7:$AB$50;$AG$7:$AG$50;$AL$7:$AL$50;$AQ$7: $AQ$50 Regards, Steve "T. Valko" wrote: I have over 400 cells to format. What cells do you want formatted? You don't have to list all 400 but list several. Is there a pattern? -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... Is there a way to copy & increment Conditional Format formulas? I have tried dragging this down & it keeps the same CF formula for every cell I copy to. I was sure to not use "$" or anything...but it doesnt seem to increment. I was hoping my formula would work since I have over 400 cells to format. I tried Paste Special & got the same result. Hmm... Also...not that I think it matters much, but the idea for the CF formula would be =B2<B1 Not sure if that changes the thought. "Bob Phillips" wrote: Why not just use =B2=B1 and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "SteveMax" wrote in message ... Hi, I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Format Formula Issue
Glad you got it working. Thanks for the feedback!
-- Biff Microsoft Excel MVP "SteveMax" wrote in message ... I tried this & to my surprise it "almost" worked for me. By almost, I mean that it formatted cells where data is equal 2 consecutives rows...but anything 2 did not format. I tried a slight variance, however, and it seems to have solved my problem. I changed the formula entered in C7 to =C7<C8(as opposed to =C8<C7) And I only entered it in C7(w/o highlighting C7:C50)...then copy&paste specialformats to every other cell. Oddly enough, I could swear I tried it that way before. I'm not necessarily convinced the variance made the difference, as I had other CF's in play as well...but it is working perfectly now & I am extremely happy with the results. Thank you for the help Biff. Regards, Steve "T. Valko" wrote: Try this: I don't know if you need/want to account for empty cells or not. Select the range C7:C50. C7 is the active cell. It's fill color is clear while the other selected cells have a blueish color. Goto FormatConditional Formatting Formula Is: =C8<C7 Click the Format button Select the Borders tab Select the line style and the location for the border OK out With the range C7:C50 still selected Goto EditCopy Then select your other ranges: H7:H50; M7:M50; R7:R50 etc Then Goto EditPaste SpecialFormatsOK -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... the cells do have a definitive pattern which will remain static. The contents within the cells will change dynamically(FYI, may not matter). There are 2 ways I believe which satisfy the same result/formatting I'm trying to get. Each row would need to be incremented as it goes down the column though. Formula placed in C7 =C7<C8 Formatting should render a bottom outline in C7 if TRUE. Formula placed in C7 =C7<C6 Formatting should render a top outline in C7 if TRUE. I know C6 is not in the range of cells listed below, but I think either method ends up comparing to a cell out of range at one end or the other(i.e. C51 or C6). I plan to format for blanks, so even if C7 or C50 are blank(which C6 & C51 are as well), they should still render the correct result, as I'll have the blank value CF as a precedent...or at least thats the plan. Cells for formatting a $C$7:$C$50;$H$7:$H$50;$M$7:$M$50;$R$7:$R$50;$W$7:$ W$50;$AB$7:$AB$50;$AG$7:$AG$50;$AL$7:$AL$50;$AQ$7: $AQ$50 Regards, Steve "T. Valko" wrote: I have over 400 cells to format. What cells do you want formatted? You don't have to list all 400 but list several. Is there a pattern? -- Biff Microsoft Excel MVP "SteveMax" wrote in message ... Is there a way to copy & increment Conditional Format formulas? I have tried dragging this down & it keeps the same CF formula for every cell I copy to. I was sure to not use "$" or anything...but it doesnt seem to increment. I was hoping my formula would work since I have over 400 cells to format. I tried Paste Special & got the same result. Hmm... Also...not that I think it matters much, but the idea for the CF formula would be =B2<B1 Not sure if that changes the thought. "Bob Phillips" wrote: Why not just use =B2=B1 and copy down -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "SteveMax" wrote in message ... Hi, I am trying to use CF on a non-contiguous range of cells. I'm basically trying to tell it to place a top border on the cell if it is NOT the same as the cell above it. I have also tried only contiguous cells, with no luck either(and would certainly have no problems applying it multiple times if a solution required doing so). The only way it has sort of "worked" is to enter it in only 1 cell, however, the moment I add it to another cell, it stops working for both cells(or any value when I try to use the formula on 1 cells). I originally had other CF's applied, but even deleting those rules does not get this to function for me. Is this formula too volatile for what i'm trying to accomplish? Or do I have something else wrong with it? =IF(CELL("contents")<CELL("contents",INDIRECT(SUB STITUTE(CELL("address"),ROW(),ROW()-1))),TRUE,FALSE) Regards, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for Conditional Format | Excel Discussion (Misc queries) | |||
Conditional Format Issue | Excel Worksheet Functions | |||
Conditional Format Issue | Excel Worksheet Functions | |||
Conditional format with a formula | Excel Worksheet Functions | |||
imported impromtu report date issue - fix by format or formula | Excel Discussion (Misc queries) |