[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.


 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,016
Messages
5,834,941
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top