| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 43882 | 2004-03-30 07:33:00 | XL query | B.M. (505) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 226002 | 2004-03-30 07:33:00 | One for the XL wiz’s. I’ve got a column of numbers in column “A”. Say number1 from A1:A10 and then changes to the number 2 from A11: A15 and then 3 from A16: A20 and so on. (This could be days of the month). Now, what I want to do is automatically rule off, i.e. bold underline, A10:M10 and then A15:M15 and then A20:M20 and so on where the number in column A changes. Not difficult to do manually, but I thought I’d like to do it automatically. Any suggestions? :) |
B.M. (505) | ||
| 226003 | 2004-03-30 08:39:00 | Try conditional format. In cell A2 use the condition equation like int(a1)<>int(a2). and set the format to underline. Copy this format to all values in column A. Careful when you type in the equation as xl defaults to eg '$A$1' - you want the relative address ie A! not the absolute address $A$1 | jeep (1673) | ||
| 226004 | 2004-03-30 22:55:00 | Worked a treat on its own Jeep. However, it seems to conflict with other conditional formatting present. For example, column A has the conditional format =MOD(A6,2)=0 This is copied down to A188 and the format sets the background colour to yellow for all even numbers. Now, when I apply my new underlining format the underlining works perfect but the last of all the even cells that are supposed to be yellow ar’nt. Always something aye? :D |
B.M. (505) | ||
| 226005 | 2004-03-30 23:31:00 | Think I've found the answer in the XL help :( Conditional formats are not applied correctly. Check for multiple conditions If you specify multiple conditions and more than one condition is met, Microsoft Excel applies only the formats for the first true condition. See whether conditions overlap If you specify conditions that overlap, Excel applies only the format of the first true condition. For example, if you specify a condition that applies yellow shading to cell values between 100 and 200, inclusive, and then you specify a second condition that applies red shading to cell values below 120, the cell values of 100 through 119.999999999999 will appear with yellow shading. It’s best to avoid overlapping conditions. Looks like we can't do it this way, any other suggestions? :( |
B.M. (505) | ||
| 226006 | 2004-03-31 00:21:00 | How about then use =and(MOD(A6,2)=0,int(a1)<>aint(a2)) in one condition (format to colour plus underline) and =and(MOD(A6,2)=0,int(a1)<>aint(a2)) (format to colour and no underline in another condition | jeep (1673) | ||
| 226007 | 2004-03-31 00:22:00 | sorry that should read... How about then use =and(MOD(A6,2)=0,int(a1)<>int(a2)) in one condition (format to colour plus underline) and =and(MOD(A6,2)=0,int(a1)=int(a2)) (format to colour and no underline in another condition |
jeep (1673) | ||
| 226008 | 2004-03-31 03:40:00 | Nearly Jeep, nearly, but not quite. I’ve changed your formula a fraction (after trying it of course) but no matter what I do I can’t get it quite right. :( I have managed things I couldn’t have possibly achieved had I been asked. Unfortunately, none of the variations was quite what I want. :D However, your suggestions have certainly opened up a whole new line of thinking. |
B.M. (505) | ||
| 1 | |||||