Forum Home
Press F1
 
Thread ID: 107717 2010-02-27 02:17:00 Excel formula Myth (110) Press F1
Post ID Timestamp Content User
861996 2010-02-27 06:24:00 Thank you... that works :)

Now, if I were to take it further... If I want do this:
=A$5<A$8
=A$6<A$6
=A$7<A$7
=A$8<A$8
and so on; is there an easier way to do this?


sorry,
that should have been:
=A$5<D$5
=A$6<D$6
=A$7<D$7
=A$8<D$8

Also: =A$5<A$8 format works
but when I add the () with the true false it fails to do anything 		Myth (110)
861997 2010-02-27 06:47:00 Formula should be

=IF(A$5<A$8,"ORDER","")

but this is in another cell not in a conditional format

Trev 		TeejayR (4271)
861998 2010-02-27 06:50:00 sorry,
that should have been:
=A$5<D$5
=A$6<D$6
=A$7<D$7
=A$8<D$8

Are you wanting to test all these conditions and do something if they are all true

- if so you can use the AND formula

Trev 		TeejayR (4271)
861999 2010-02-27 07:23:00 IM wanting to test conditions on a line by line basis

because A$4 might be less than D$4 but B$5 might be great than D$5

I dont want them all coming up as red becasue only onwe line meets the conditions (if you know what I mean) 		Myth (110)
862000 2010-02-27 18:50:00 As part of my job, I am required to maintain stock levels
AS part of this I have an excel page with all our stock part numbers, and the current levels. I also have a column which has a suggested order point (taking into account lead-times of suppliers etc)

What I want to do (without enabling macros) is this:
If the balance in column A5 is less than the balance in A8, then either:
1, highlight the background for the entire range A1-A8 (preferred); or
2, highlight column A5 in red bold font (least preferred); or
3, have some red ** in column A9

Is this possible in excel?

Sorry I just misunderstood point 1

You can achieve this by taking the $ (prior to the row number) out of the test - you also need to make sure there are no quotes around the formula.

I just tested this by doing the test on cell A5 then using the format painter to copy the conditional test to the next cell a6 - I then copied cell a6's format to a7 and so on. This won't be a problem if you only have to test a few rows but if you have to test 100's p[erhaps you could consider flipping your row and column layouts

Trev 		TeejayR (4271)
862001 2010-02-27 19:51:00 Umm, I think we are both lost lol

Disregard the first post for now, as I have it highlighting A5 (as an example) when it meets the condition (it's value is lower than D5)

Now, what I want to achieve is - with one command, it is possible to have this conditional format applied to A6, A7, A8 and so on if their values are lower than their relevant comparison cells

i.e.
with one command for the table, can I highlight:
A5 if its value is lower than D5, else don't highlight
A6 if its value is lower than D6, else don't highlight
A7 if its value is lower than D7, else don't highlight
A8 if its value is lower than D8, else don't highlight
etc etc..

or do I need to do a command for each line? 		Myth (110)
862002 2010-02-27 22:30:00 I set up a spreadsheet with the following columns
A=Product number
B=Weekly Sales
C=10 weeks Sales
D=Stock on Hand
E=Order ?

First I created the Order test

In cell E5 I tested if cell D5 was less than c5 if not I created the word Order
using the formula =IF(D5<C5,"Order","")

I then selected cells A5:E14 and created the following conditional formula

=$E5="Order" and for the "Applies To" range =$A$5:$E$14 if you use the red fill with a white font the columns A:E (for the appropriate rows) are highlit when you need to order your stock

If you only want to make the "Order" highlight then you set the "Applies To" range to be $E$5:$E$14

Hope this helps

Trev 		TeejayR (4271)
862003 2010-02-28 02:04:00 Sweet,
Thank you for your help. :D

I made a slight mod, and now it works how I want 		Myth (110)
862004 2010-02-28 05:47:00 Glad to hear it

Trev 		TeejayR (4271)
1 2