[Excel 2007] conditional formatting how to

[Whyte_Rhyno]

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.

 

[asking]

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]

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]

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.