You can use Conditional Formatting for this. I'm going to assume you're using 2003? I believe that is the majority of users these days, since you didn't specify. CF is dependent on what cell(s) you have selected and what cell is active (note the singularity of 'active', as you can only have 1 active cell, but can have many cells in the selection, a VERY important distinction, especially with CF). Please describe for us how your data is structured and the entire range you want CF setup on.
Basically, in 2003, it would be to select your cell(s), pay attention to the name box, which will show you the address of the active cell, then go to Format | Conditional Formatting, set the formula to whatever you wanted. So if you selected all of A and the activecell was A1 you could use the formula...
Code:
=$A1="B"
Then set the format for that. Do the same for all of your other conditions. There is a limit of 3 conditions you can use, but if you have (or upgrade to) 2007 that limit is taken away and you now have a Conditional Format Manager, which is really nice.
If, however, you are wanting to affect another cells result (displayed value, as opposed to format), then you'll need a formula.
But what is stopping you from must formatting the column B?? Can you post a sample file? I do not understand why you can't just input negative numbers for expense and positive numbers for income. Isn't that what they are? Or are you just being lazy and wanting the program to do it all for you?
Well, having each item out like their own record has its advantages, and would probably be the way I would go. This leaves better data structure (IMHO) to manipulate later on, especially for things like pivot tables.
To format the data, select the cell(s) in question (or perhaps an entire column) and press Ctrl + 1. There are number formats natively which will do what you're asking.
First observation is that you cannot just type a value into a cell and expect Excel to understand that each record with d in column B, should have red text.
As Zack says, you need to type the value with a minus infront and then format the value cells.
As far as I am concerned unless you are prepared to put a minus in front of the value, then you need to go to two columns as I already suggested.
Per Zack and The Vilan's suggestions, I would enter this in the adjacent cells in column D
=IF(B12="d",C12*-1,C12)
and then format those cells in D as numbers which turn red when the number is negative.
I attached a sample sheet. I tried Zack and The Villan's suggestions, formatted cells as currency to show as red font when negative, and also manually input the minus sign.
Based on the attached sheet, is there a way to get a running balance for EACH agent.
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!