2009/11/7 Johnny Rosenberg <[email protected]>: > 2009/11/6 Walter Hildebrandt <[email protected]>: >> Cell A1 has a number. >> Cell B1 is to have how much less, or much more, A1 is than 40. >> When A1 is less than 40 the answer in B1 is to be in black. >> When A1 is more than 40 the answer in B1 is to be in red. >> . >> This means if A1 is 50, the 10 in B1 is red. When A1 is 30, the -10 in B1 >> is black. >> > > Have you tried the STYLE command? > > First create a style with red text, let's call it RedText. If you > don't know how to crate styles, press F11 and experiment a bit from > there. Hint: Right clicking an existing style might be a good start… > > Now, in B1, enter your formula: > =A1-40+IF(A1>40;STYLE("RedText");0) > > The STYLE function always returns 0, so the formula is mathematically > equal to A1-40+0. > > Using conditional formatting will also work. This is just another way > to do the same thing in this case. In some cases conditional > formatting is the better choice, in some cases this solution is the > one to prefer. > > A bit OT: > I'll give you an example where the use of STYLE is exceptionally good: > Let's say that you have a cell than can contain some words (maybe > names of people?). Maybe we have a list of when someone will do what. > Maybe we have a certain task to do and we decided that we all would > share the job. > Everyone can take a look on the list to see when he or she is supposed > to do the task. > Well, if every one of us was assigned a specific colour, it would be > easy to see your name in the list, right? So my name maybe will be > displayed on a red background, your with a blue backgound, Richard is > yellow, Gene is green (which is a rhyme, by the way…) and Brian is > Magenta. Well, first we create one style for each person. Give each > style the same name as the person associated with it. > > To do this the easy way (maybe there is even easier ways, please tell > me in that case), use one sheet for inputing data and another sheet > for viewing it. Name the sheets, maybe ”Input” and ”View”. > Let's say that we use column A for date and column B for peoples > names. Let's also say the the first row is for headers. > > So input a date in A2 and a name in B2 and continue with A3, B3 and so > on. All this is done on the Input sheet, of course. > Now switch to the View sheet. > Copy the headers from the Input Sheet. A good way (I think) to do this > is to just type ”=Input.A1” (without the quotes, of course, like in > the rest of my examples in this message).
Type this in A1 on the View sheet, of course. Seems like I forgot to mention that… Johnny Rosenberg >Now you can copy this cell > where ever you want in the View sheet, and it will display the value > of the corresponding cell on the Input sheet. This way, if you change > your headings or other cells, you only need to change them on the > Input sheet, which is a good thing in most cases. > > Ok, now input the following in B2: > =LEFT(Input.B2 & STYLE(Input.B2);LEN(Input.B2)) > Highlight B2 (still on the View sheet) and copy it down as far as you need. > > Without the LEFT function, Walter would have been turned into Walter0, > for example, and that doesn't look very good… > > This way we are not limited to the three conditions of conditional > formatting. If we are 100 people, we ”only” need to create 100 styles > to make it work. The tough thing in this case is maybe to find 100 > different colours that doesn't look like each other… > > Well, this was only an example and it works (I actually tested it > while I wrote this message). > > Johnny Rosenberg > --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
