2007/4/12, Johnny Andersson <[EMAIL PROTECTED]>:
I just forgot to mention that this method doesn't work if you actually WANT $0.00 to be shown if the result actually is 0. In that case you will need the IF thing after all, I guess. Example 1, the formatting method: [Empty] → [Empty] 0 → [Empty] 1 → $1.00 Example 2, the IF method: [Empty] → [Empty] 0 → $0.00 1 → $1.00 If you want the results of example 2, I am afraid that you need the IF statement, sorry.
Actually I tested another thing, which didn't work, but since I think it would be nice if it did, I think I am going to suggest it as an enhancement in IssueZilla, would anyone think this would be a good idea? The idea is the following: Based on format codes, such as: *[>76]0" - Great!";[<30]0" - Work a little harder!";0" - Quite alright." ** * Yes, a silly example, but it works. However, it would be nice if this worked too, which it doesn't: *[>0]0" is today's number";[=""]"Sorry, no number today";0" is too low…" *When I try this, [=""] is automatically converted to [=0], which is not what I want. If this worked, it would be possible to solve the problem that 0 is showed instead of an empty cell, or an empty cell is shown instead of 0, unless an IF statement is used in the cell formula. * *There IS a way to solve it without an IF, statement, but it also has its limits: You can, of course, use Format → Conditional formatting… if you like to. There are probably a few ways of doing this, here are two ways: The example is: The formula in Sheet1.A1 is: *=Sheet2.A1* 1. Create a style with the background colour set to the same colour as the character colour. Its name could be Empty. Format → Conditional formatting… → Formula is → Sheet2.A1="" → Select Empty in the Cell Style drop down field → OK. One of the disadvanteges is that if you change the background colour of all cells, you also need to change the character colour of the Style "Empty" to the same colour (otherwise you will see the zero anyway). Maybe there is a workaround for this. 2. The same as 1, but instead of manipulating with colour, use the Cell Protection → Hide All option for the "Empty" style. This, however, will only work if you protect the sheet. Johnny Andersson
