Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
My 2 cell conditional format works perfect,
But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
On Feb 8, 9:04*am, Mark wrote:
My 2 cell conditional format works perfect, But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") *Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks. As you mentioned yourself, if you remove the $ sign, the problem will be solved. That $ signs, keep the row number to be a fixed number upon copying to other rows. bring the cursor to the dollar sign and press F4 key, you will see four possible combination and choose the one with no $ sign. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
As you mentioned yourself, if you remove the $ sign, the problem will be
solved. That $ signs, keep the row number to be a fixed number upon copying to other rows. bring the cursor to the dollar sign and press F4 key, you will see four possible combination and choose the one with no $ sign. -- R. Khoshravan Please click "Yes" if it is helpful. "Mark" wrote: My 2 cell conditional format works perfect, But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
Completely changes the acton on the work sheet. Is there n work arounf or a
formula to get the exact same results without trmoving the $ I needed to keep cells blank until data hit a minimum of 1 cell. Then The conditionals. If yu remove the $'s, when you delete data that it pertains to. The last color standing remains. Could you look at my Formula in O7, then look ay the conditionals, and find a way for them to act the same, AND BE ABLE TO COPY DOWN THE PAGE "Mark" wrote: My 2 cell conditional format works perfect, But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
Completely changes the acton on the work sheet. Is there n work arounf or a
formula to get the exact same results without trmoving the $ I needed to keep cells blank until data hit a minimum of 1 cell. Then The conditionals. If yu remove the $'s, when you delete data that it pertains to. The last color standing remains. Could you look at my Formula in O7, then look ay the conditionals, and find a way for them to act the same, AND BE ABLE TO COPY DOWN THE PAGE "Khoshravan" wrote: As you mentioned yourself, if you remove the $ sign, the problem will be solved. That $ signs, keep the row number to be a fixed number upon copying to other rows. bring the cursor to the dollar sign and press F4 key, you will see four possible combination and choose the one with no $ sign. -- R. Khoshravan Please click "Yes" if it is helpful. "Mark" wrote: My 2 cell conditional format works perfect, But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
The complexity lies in the intended propagation of the CF formula
Tinker with it like this, which applies it at one go into the entire range. Lightly tested here, seems to function just right Select O6:O871 (with O6 active), apply the CF using Formula Is: Condition 1: =AND(OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<"",OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<=0) Format Green Condition 2: =AND(OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<"",OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)0) Format Red Ok out, then test it .. I took the liberty of changing slightly the 2nd condition ("0" used instead of "=1") so that there are no gaps with the 1st condition If the above helps in any/some way, press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mark" wrote: Completely changes the acton on the work sheet. Is there n work arounf or a formula to get the exact same results without trmoving the $ I needed to keep cells blank until data hit a minimum of 1 cell. Then The conditionals. If yu remove the $'s, when you delete data that it pertains to. The last color standing remains. Could you look at my Formula in O7, then look ay the conditionals, and find a way for them to act the same, AND BE ABLE TO COPY DOWN THE PAGE |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
I certainly can remove the $ in the ways you describe. But, then the cells
remain at the last color they were at if a date in O5 is removed. That makes for a lot of color. I will try what Alojz oferred, barring learning how to do the test! Mark Thanks David. "Khoshravan" wrote: As you mentioned yourself, if you remove the $ sign, the problem will be solved. That $ signs, keep the row number to be a fixed number upon copying to other rows. bring the cursor to the dollar sign and press F4 key, you will see four possible combination and choose the one with no $ sign. -- R. Khoshravan Please click "Yes" if it is helpful. "Mark" wrote: My 2 cell conditional format works perfect, But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
Mark,
I amended your file and sent it back to you - did you receive it, and did it do what you wanted? Pete On Feb 8, 7:55*pm, Mark wrote: I certainly can remove the $ in the ways you describe. But, then the cells remain at the last color they were at if a date in O5 is removed. That makes for a lot of color. I will try what Alojz oferred, barring learning how to do the test! Mark Thanks David. "Khoshravan" wrote: As you mentioned yourself, if you remove the $ sign, the problem will be solved. That $ signs, keep the row number to be a fixed number upon copying to other rows. bring the cursor to the dollar sign and press F4 key, you will see four possible combination and choose the one with no $ sign. -- R. Khoshravan Please click "Yes" if it is helpful. "Mark" wrote: My 2 cell conditional format works perfect, But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") *Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks.- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
Reading and inferring between the lines from the later responses since
posted, it looks like my interp of your issue was quite off. Save the suggestion as a way for when you meant to propagate it as described in the response, ie when you want to CF a pair of 2 consecutive columnar cells, where the conditions are affixed to the value in the 2nd cell in each pair down the range. Bottoms up! -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
Yes of course, I thought maybe, I had become heavy. was gone most of the day.
Your formulas were Spot on, and The sheet is great. I need to include in the conditionals, for P5,6,7(ship Date) If D6:N6 are blank the conditionals don't kick in. I need the exact same condtionals you wrote but I need (P7) to calulate the difference between P5 and O5 and have the conditionals still work (EVEN if) D6:N6 are BLANK Reason. Sometimes D6 row will not get a date. and There will still be an entry in P5. All About P7 This is what you wrote: =AND(P7<"",P7<=0) Green =AND(P7<"",P7=1) Red Make sense. Your an MVP Pete.!! "Pete_UK" wrote: Mark, I amended your file and sent it back to you - did you receive it, and did it do what you wanted? Pete On Feb 8, 7:55 pm, Mark wrote: I certainly can remove the $ in the ways you describe. But, then the cells remain at the last color they were at if a date in O5 is removed. That makes for a lot of color. I will try what Alojz oferred, barring learning how to do the test! Mark Thanks David. "Khoshravan" wrote: As you mentioned yourself, if you remove the $ sign, the problem will be solved. That $ signs, keep the row number to be a fixed number upon copying to other rows. bring the cursor to the dollar sign and press F4 key, you will see four possible combination and choose the one with no $ sign. -- R. Khoshravan Please click "Yes" if it is helpful. "Mark" wrote: My 2 cell conditional format works perfect, But I can't copy a 4 cel copy and Paste special down the column , I think because the $ signs are in th conditionals. I'm not sure why they are there, but I know if I remove the $ sign for the copy paste special or past, My conditionals Split colors or remain. Formula in O7 is: =IF(COUNT(D7:N7)0,SUM(D7:N7),"") Conditional formulas in O6,O7 =AND(O$7<"",O$7<=0) Green =AND(O$7<"",O$7=1) Red I wanted cells until data appeared. All works fine, cept. I can not copy it down some 800 lines thru O871 I have text every 4th line. IS there a way to copy this down and have he cell numbers change with it, an keep the integrity of the Formula and the conditionals? Thanks.- Hide quoted text - - Show quoted text - |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
I din't see this till late.
That is some incredible formula. I will try it on a test sheet, before I torpedo 871 lines. Which I've done more than once now. Thanks you MAX. Pete has been great with me also, he has quite the handle on what really is a simple tracking sheet. It's me that doesn't know much at all is the problem, and I got in oer my head.. Mark "Max" wrote: The complexity lies in the intended propagation of the CF formula Tinker with it like this, which applies it at one go into the entire range. Lightly tested here, seems to function just right Select O6:O871 (with O6 active), apply the CF using Formula Is: Condition 1: =AND(OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<"",OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<=0) Format Green Condition 2: =AND(OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)<"",OFFSET(O$6,(INT((ROWS($1:1)-1)/2)+1)*2-1,)0) Format Red Ok out, then test it .. I took the liberty of changing slightly the 2nd condition ("0" used instead of "=1") so that there are no gaps with the 1st condition If the above helps in any/some way, press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mark" wrote: Completely changes the acton on the work sheet. Is there n work arounf or a formula to get the exact same results without trmoving the $ I needed to keep cells blank until data hit a minimum of 1 cell. Then The conditionals. If yu remove the $'s, when you delete data that it pertains to. The last color standing remains. Could you look at my Formula in O7, then look ay the conditionals, and find a way for them to act the same, AND BE ABLE TO COPY DOWN THE PAGE |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
Over my head!!! Period!
Thanks. Mark "Max" wrote: Reading and inferring between the lines from the later responses since posted, it looks like my interp of your issue was quite off. Save the suggestion as a way for when you meant to propagate it as described in the response, ie when you want to CF a pair of 2 consecutive columnar cells, where the conditions are affixed to the value in the 2nd cell in each pair down the range. Bottoms up! -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
copying conditon formats with $
No prob, Mark. You're welcome.
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Mark" wrote in message ... I din't see this till late. That is some incredible formula. I will try it on a test sheet, before I torpedo 871 lines. Which I've done more than once now. Thanks you MAX. Pete has been great with me also, he has quite the handle on what really is a simple tracking sheet. It's me that doesn't know much at all is the problem, and I got in oer my head.. Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formats - column widths, formats, outlining to worksheets | Excel Worksheet Functions | |||
Get data with certain conditon | Excel Discussion (Misc queries) | |||
How to determine the number from given conditon? | Excel Discussion (Misc queries) | |||
Copying Formats | Excel Discussion (Misc queries) | |||
copying conditional formats | Excel Worksheet Functions |