On Sun March 11 2007 11:25, Brian Barker wrote: > At 09:46 11/03/2007 -0800, you wrote: > >The following formula stopped working when I started a new sheet for > >2007. The part that is not working is the test of the cell I7. I > >have tried changing the format to general, number and text. No > >change. The only way I can get the formula to work is if I put a > >reference in I7, such as "=A1" to another cell with the "E" in > >it. This formula is used in a cash flow analysis sheet. > > I've tried this out, and it works as I'd expect in my installation of > Calc (2.0.2). > > >=IF(I7="E";L6+F7-G7;L6+F7) > > Did you copy this and paste it into your message, or retype it? It's > very easy to have a simple mistype in such a formula, especially when > it is shown in a sans serif typeface. You could have a figure one or > letter ell for a letter eye (giving "17" or "l7" for "I7"), or > perhaps a colon for a semi-colon? But that's unlikely in this case, > since the behaviour you describe when you put a reference in I7 tells > us that the suspect expression is indeed relying on what's in I7. > > What is actually in I7? Try copying and pasting the entire contents > into, say, OpenOffice Writer. Use Edit | Paste Special... (not > Paste) and then select "Unformatted text". Switch on View | > Nonprinting Characters, and see what you really have in I7. Is > there, perhaps, a leading or trailing blank along with the letter > "E"? Incidentally, there exists a CLEAN function, which removes > nonprinting characters from a string, so - if there is a possibility > of the cell containing extraneous characters and if you want the "E" > to be seen as such despite this - you can simply change your > reference to "I7" to be to "CLEAN(I7)" instead. Alternatively, try > "LEFT(TRIM(I7))". TRIM first removes leading blanks, and LEFT then > takes the leftmost single character (by default of a second argument) > from what remains. > > There is another test for what is in I7. Put the expression > "=LEN(I7)" into another cell. What do you see? The length of the > character string "E" should be one, of course. If the result is > anything else, there's something else in I7! > > At 10:37 11/03/2007 -0800, John R. Sowden wrote: > >There are no quotes around the E. > > Good-oh! > > >I believe the issue is the format of the cell holding the E. > > You say you have changed the format to general, number and > text. Have you tried changing the formatting to "default"? (Use > right-click or the Format menu.) > > I suppose the next thing is to send someone a copy of your document > and see if they can see what's happening. > > Brian Barker - privately
I copied your reply to the group, as you found the solution, and I thought others should benefit. The clean(I7) function had no effect. The left(trim(I7)) function solved the problem. To me this means that there must be something else in the test cell other than E I am using the current version of OOo, v1. In Suse 9.3 Linux. What I do not understand is the previous sheets are also same op sys, same ver, without the benefit of the left(trim()) The previous sheets had the target cell (I7) formatted as text, as that is all that was in that cell. I have tried that on this sheet with no difference. I was going to question OOo upgrades, but I just called up 2006 to check the format, and the formula is working fine. Hmmmmm...... In any event, thanks for the solution! -- John R. Sowden AMERICAN SENTRY SYSTEMS, INC. Residential & Commercial Alarm Service UL Listed Central Station Serving the San Francisco Bay Area Since 1967 [EMAIL PROTECTED] www.americansentry.net --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
