[Excel 2007] conditional formatting how to

Whyte_Rhyno

New Member
Joined
Mar 30, 2011
Messages
2
Hello all,

Been lurking here for a few weeks now picking up new techniques, I want to thank you all for the great tutorials and knowledge you guys have shared.

I'm in the midst of a couple of book tutorials but have become stuck while trying to improve on a particular exercise.

I have 4 departments with a 2 tier staffing level and allocation of funds (a formula) is based upon the number of staff in each, as well as the number of tier 1 staff.


Problem:

Basically, I'm looking for a way that allows for a condition that if the "Directors" department has less than, say, £32,000, there's a pop-up that informs the user that the Directors do not have enough funds.

This is obviously quite easy with a conditional format, however, data can be sorted numerically and alphabetically and when it's done so, the other departments within the cell get the conditional format. I want just the directors to be conditionally formatted.

I am a newbie to Excel, so I'm no doubt missing the obvious.

I appreciate you time. Attached is a jpg of my basic spreadsheet.


 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

asking

Board Regular
Joined
Sep 5, 2007
Messages
226
No pop up, just conditional formatting.

Try ~ Select all your Data in column B starting at B4
select conditional formatting, select new rule, under rule type, select use a formula.....
enter this formula ~
=AND($B4="Directors",$E4<32000)

Then format as you wish, click ok, click apply.
 

Whyte_Rhyno

New Member
Joined
Mar 30, 2011
Messages
2
No pop up, just conditional formatting.

Try ~ Select all your Data in column B starting at B4
select conditional formatting, select new rule, under rule type, select use a formula.....
enter this formula ~
=AND($B4="Directors",$E4<32000)

Then format as you wish, click ok, click apply.

That worked an absolute treat! Thank you very much for your help! +rep

On a cheeky side note, I'm trying to ensure that values entered are amounts of money, but when I use data validation, anything I enter (string/integers/decimals etc) gets rejected.

For example if I enter 5,198.00, it's not allowing the value to be entered, even though it's a correct value.

Sorry for the continued newbieness.
 

asking

Board Regular
Joined
Sep 5, 2007
Messages
226
Try ~
Select the cell you want to as a money cell, go to data validation, In the allow box select decimal, In the next box down(data) select Greater than, in the next box down(minimum) enter 0 (zero), in the check box bottom left(apply to....) tick the box.

If you need to enter negative amounts of money instead of entering 0 in the minimum box enter -10000 (more than -10000 if you have bigger negative amounts)

The cell and all like cells will now accept any entry that evaluates to money.(a decimal) eg. if you enter =5*4.80 it will accept it but if you try to enter text it will stop you and tell you to retry.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,172
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top