PDA

View Full Version : XL query



B.M.
30-03-2004, 07:33 PM
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? :)

jeep
30-03-2004, 08:39 PM
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

B.M.
31-03-2004, 10:55 AM
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.
31-03-2004, 11:31 AM
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? :(

jeep
31-03-2004, 12:21 PM
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
31-03-2004, 12:22 PM
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

B.M.
31-03-2004, 03:40 PM
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.