| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Office 2007 Conditional Formatting
I have the following formula which highlighs the lowest five of ten numbers.
=ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007
This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Bernard;
Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
If you select all the row, then the condition will apply to the multi-row
range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Bernard;
I've tried both of your suggestions. Both "retain" the highlighting after Save & Close. That is the good news. The bad news; Solution #1 using "Top/Bottom" works in the first Row (Row 10). Used Format Painter for Rows 11:138 (this particular WS uses 138 Rows) which only highlighted the lowest ONE of all the scores in the Range H11:Z138. Solution #2 works very well except, again using the Format Painter, only paints through Row 100. I've done this one several times with the same result. I would use this one if I could get it to work past Row 100. If you would consider looking at my WS, I could send it VIA email. Bob "Bernard Liengme" wrote: If you select all the row, then the condition will apply to the multi-row range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Yes, do send me a file
Get my email from my website, please -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; I've tried both of your suggestions. Both "retain" the highlighting after Save & Close. That is the good news. The bad news; Solution #1 using "Top/Bottom" works in the first Row (Row 10). Used Format Painter for Rows 11:138 (this particular WS uses 138 Rows) which only highlighted the lowest ONE of all the scores in the Range H11:Z138. Solution #2 works very well except, again using the Format Painter, only paints through Row 100. I've done this one several times with the same result. I would use this one if I could get it to work past Row 100. If you would consider looking at my WS, I could send it VIA email. Bob "Bernard Liengme" wrote: If you select all the row, then the condition will apply to the multi-row range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Bernard,
Did you receive my email? Bob "Bernard Liengme" wrote: Yes, do send me a file Get my email from my website, please -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; I've tried both of your suggestions. Both "retain" the highlighting after Save & Close. That is the good news. The bad news; Solution #1 using "Top/Bottom" works in the first Row (Row 10). Used Format Painter for Rows 11:138 (this particular WS uses 138 Rows) which only highlighted the lowest ONE of all the scores in the Range H11:Z138. Solution #2 works very well except, again using the Format Painter, only paints through Row 100. I've done this one several times with the same result. I would use this one if I could get it to work past Row 100. If you would consider looking at my WS, I could send it VIA email. Bob "Bernard Liengme" wrote: If you select all the row, then the condition will apply to the multi-row range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Not yet
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard, Did you receive my email? Bob "Bernard Liengme" wrote: Yes, do send me a file Get my email from my website, please -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; I've tried both of your suggestions. Both "retain" the highlighting after Save & Close. That is the good news. The bad news; Solution #1 using "Top/Bottom" works in the first Row (Row 10). Used Format Painter for Rows 11:138 (this particular WS uses 138 Rows) which only highlighted the lowest ONE of all the scores in the Range H11:Z138. Solution #2 works very well except, again using the Format Painter, only paints through Row 100. I've done this one several times with the same result. I would use this one if I could get it to work past Row 100. If you would consider looking at my WS, I could send it VIA email. Bob "Bernard Liengme" wrote: If you select all the row, then the condition will apply to the multi-row range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Bernard,
Have you received my two WS's? I answered your reply yesterday. Bob Morris "Bernard Liengme" wrote: Yes, do send me a file Get my email from my website, please -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; I've tried both of your suggestions. Both "retain" the highlighting after Save & Close. That is the good news. The bad news; Solution #1 using "Top/Bottom" works in the first Row (Row 10). Used Format Painter for Rows 11:138 (this particular WS uses 138 Rows) which only highlighted the lowest ONE of all the scores in the Range H11:Z138. Solution #2 works very well except, again using the Format Painter, only paints through Row 100. I've done this one several times with the same result. I would use this one if I could get it to work past Row 100. If you would consider looking at my WS, I could send it VIA email. Bob "Bernard Liengme" wrote: If you select all the row, then the condition will apply to the multi-row range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
You must have got my reply by now
-- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard, Have you received my two WS's? I answered your reply yesterday. Bob Morris "Bernard Liengme" wrote: Yes, do send me a file Get my email from my website, please -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; I've tried both of your suggestions. Both "retain" the highlighting after Save & Close. That is the good news. The bad news; Solution #1 using "Top/Bottom" works in the first Row (Row 10). Used Format Painter for Rows 11:138 (this particular WS uses 138 Rows) which only highlighted the lowest ONE of all the scores in the Range H11:Z138. Solution #2 works very well except, again using the Format Painter, only paints through Row 100. I've done this one several times with the same result. I would use this one if I could get it to work past Row 100. If you would consider looking at my WS, I could send it VIA email. Bob "Bernard Liengme" wrote: If you select all the row, then the condition will apply to the multi-row range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Office 2007 Conditional Formatting
Yes I did. I sent you my thoughts last evening.
Bob "Bernard Liengme" wrote: You must have got my reply by now -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard, Have you received my two WS's? I answered your reply yesterday. Bob Morris "Bernard Liengme" wrote: Yes, do send me a file Get my email from my website, please -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; I've tried both of your suggestions. Both "retain" the highlighting after Save & Close. That is the good news. The bad news; Solution #1 using "Top/Bottom" works in the first Row (Row 10). Used Format Painter for Rows 11:138 (this particular WS uses 138 Rows) which only highlighted the lowest ONE of all the scores in the Range H11:Z138. Solution #2 works very well except, again using the Format Painter, only paints through Row 100. I've done this one several times with the same result. I would use this one if I could get it to work past Row 100. If you would consider looking at my WS, I could send it VIA email. Bob "Bernard Liengme" wrote: If you select all the row, then the condition will apply to the multi-row range: it will highlight the lowest 5 in the entire range, I entered some numbers in H10:Z15 I selected the first range (H10:Z10) and used CD to highlight the Bottom 5 Then I selected H10:Z10, clicked on the Format Painter (paintbrush icon to the left in the Home tab), and 'painted' the other 4 rows. This was successful in marked CD highlight the bottom 5 cell in each row independently Meanwhile, I am playing with your formula to see if there is a bug in Excel 2007. Alternative to my suggestion above: experiment with this Select all 200 rows, I will assume H10 is the first cell in this range For the CD formula use: =H10<SMALL($H10:$Z10,6) Note where the $ symbols go. This worked well for me best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... Bernard; Thanks for the answer. Possibly I did not completely explain what I do with my formula in CD. I can have up to 200 Rows and each Row MUST stand on it's own. I have tried using the "Format only Top/Bottom Ranked. It works for one Row but I've had no success past the one Row. Maybe I'm not entering the Range properly. My Range in this case would be H10:Z200. Bob "Bernard Liengme" wrote: Why not use the "Format only Top or Bottom Ranked" feature of Excel 2007 This is easier than messing with an array formula (as yours is) in CD best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "robert morris" wrote in message ... I have the following formula which highlighs the lowest five of ten numbers. =ISNUMBER(MATCH(H10,SMALL($H10:$Z10,COLUMN($A:$E)) ,0)) All works well until I Save and exit. When I re-open the worksheet the highlighting is gone. I can then go to CD - Manage Rules - Edit Rule - Apply and OK. At this point, all formatting reappears. This happens on all worksheets. Any ideas? Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Max with Conditional Formatting in Office 2007 | Excel Worksheet Functions | |||
| Office 2007 Conditional Formatting question | Excel Discussion (Misc queries) | |||
| condtional formatting in office 2007 is not working properly | Excel Discussion (Misc queries) | |||
| Conditional Formating - by row, not by column (Office 2007) | Excel Discussion (Misc queries) | |||
| Conditional Formating Using Office 2007 | Excel Worksheet Functions | |||
Linear Mode
