Forum Home
Press F1
 
Thread ID: 107717 2010-02-27 02:17:00 Excel formula Myth (110) Press F1
Post ID Timestamp Content User
861986 2010-02-27 02:17: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? 		Myth (110)
861987 2010-02-27 02:25:00 It's possible with conditional formatting in Excel 2007. pcuser42 (130)
861988 2010-02-27 02:29:00 I think we use Excel 2003

We connect to the server via thin clients, so I can't really get 2007 installed
This is why I also asked for no macros - they have us reasonably locked down 		Myth (110)
861989 2010-02-27 02:33:00 I have little experience with Excel 2003, but it's in there too. office.microsoft.com pcuser42 (130)
861990 2010-02-27 03:29:00 I've done this in Excel 2002/2003 using conditional formatting. You can only have two conditions though. No macros were required.

I had to use an additional column (cell) that contained a formula that generated a result eg Y or N. Your Y or N would be if the balance in column A5 is less than the balance in A8. So if A5 is less than A8, then display a "Y", otherwise display a "N".

I then used conditional formatting so that if an Y was the result, then make the Y font white (so it was invisible). If an N was present, then make the N font bold and red. This made a very obvious alert.

I couldn't say if this cell = N, then change the background (or font) in a different cell or range of cells. You can only apply the rule to the cell you are in. Hope that makes sense.

You should add the conditional formatting to whatever column does the maths for A5 and A8. 		Jen (38)
861991 2010-02-27 03:38:00 Conditional formatting will do the job nicely - just bear in mind that you're limited to only 3 levels of formatting in Excel 2003 and older; there's no constraint in Excel 2007, but too many conditions will slow your spreadsheet down considerably.

You can get around the 3 level limit in older versions by using CASE statements in VBA, which is nice and easy to do. 		nofam (9009)
861992 2010-02-27 04:25:00 OK, what is wrong with this.. IF(H4 <= K4) when added to a "If Formula" conditional format dialog?

I am quite inexperienced when adding formulae to excel 		Myth (110)
861993 2010-02-27 04:45:00 You should be able to use this is Excel 2003

Select all the cells in the range you wish to highlight (sat A1:A10) and enter the conditional formula =A$5<A$8 - the trick is to make the rows fixed so all rows in the range you wish to highlight are looking at the levels in rows 5 and 8. Then enter your display my favourite is a red fill background and a white bold font.

And whenever a5 is less than A8 cell A1:A10 are displayed as white font on red fill

You can then copy this condition across multiple columns - the test is done on the rows.

Trev 		TeejayR (4271)
861994 2010-02-27 05:22: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?

Also, if I want the word ORDER to appear in another column if the conditions are met, or nothing to be in the column if the conditions aren't met, how do I add this? 		Myth (110)
861995 2010-02-27 05:41: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?

Also, if I want the word ORDER to appear in another column if the conditions are met, or nothing to be in the column if the conditions aren't met, how do I add this?

Something wrong with your tests eg you cant test A$7<A$7 because they are the same cell

For ORDER your formula would be =If(A$5<A$8,"ORDER","")

Trev 		TeejayR (4271)
1 2