2007/4/11, Dan Cox <[EMAIL PROTECTED]>:
Here's one that I am sure will get plenty of replies: I am setting up a spread sheet to calculate payments, balance owed, etc, and I have 25 rows that can have data input (just to keep it all on one sheet). How do I keep the calculating formula, but make the cells stay blank (not show $0.00) if I do not put name or some other identifier into that row. In other words, how do I get only the rows that need to have calculated to show calculations, and have the remainder stay blank, but still ready to accept data. I do not want to hide the rows, because I am not the one who will use this spreadsheet, and that has already caused me messes in the past. Thank you in advance for the replies. Dan Sincerely, Dan Cox, CEO
Hi again! I experimented a bit with currency formatting and I came up with the following solution, so that $0.00 will be shown as blanc: Right click the cell → Format Cells… → Click "Numbers" tab if it's not already shown → Select "Currency" in the "Category" field → Select something that looks good in the "Format" field, such as "-$1,234.00" → Add ";#" to the end of what showed up in the "Format code" field. It may look like this, in your case: [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;# It works like this: # means that a numerical character is shown if it's not zero. 0 means that a numerical character will always be shown. There are also two semi colons, and they work like this: Code1;Code2;Code3 If the value of the cell > 0, Code1 will be applied. If the value of the cell < 0, Code2 will be applied. If the value of the cell = 0, Code3 will be applied. In this case Code1 = [$$-409]#,##0.00 Code2 = [RED]-[$$-409]#,##0.00 Code3 = # So if the cell value = 0, then Code3 will be applied which is # which means that nothing is shown. The [$$-409] is probably just to get the right currency symbol and things like that. This will also work: [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;"" [$$-409]#,##0.00;[RED]-[$$-409]#,##0.00;@ This is fun, because I didn't think of this solution before! I always did the IF thing! This could save me a lot of work in the future! Thanks to you! For normal two decimal numbers, with no special formatting, the code is as easy as "0.00;-0.00;#" or "#,##0.00;-#,##0.00;#"! I also tried this, which ALSO worked: B12: Hello C12: =B12 C12 format: 0;0;# (#;#;# and @;@;@ etc. will also work, because the important thing is what's after the second semi colon) C12 shows: Hello Delete the contents of B12, then C12 now shows NOTHING! Just as it should be (in most cases anyway)! Before formatting it showed 0 when B12 was empty! Johnny Andersson
