From ooo-users-return-375-apmail-incubator-ooo-users-archive=incubator.apache.org@incubator.apache.org Tue Dec 13 14:38:35 2011 Return-Path: X-Original-To: apmail-incubator-ooo-users-archive@minotaur.apache.org Delivered-To: apmail-incubator-ooo-users-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id AFCE47968 for ; Tue, 13 Dec 2011 14:38:35 +0000 (UTC) Received: (qmail 59235 invoked by uid 500); 13 Dec 2011 14:38:35 -0000 Delivered-To: apmail-incubator-ooo-users-archive@incubator.apache.org Received: (qmail 59202 invoked by uid 500); 13 Dec 2011 14:38:34 -0000 Mailing-List: contact ooo-users-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: ooo-users@incubator.apache.org Delivered-To: mailing list ooo-users@incubator.apache.org Received: (qmail 59194 invoked by uid 99); 13 Dec 2011 14:38:34 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 13 Dec 2011 14:38:34 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=5.0 tests=RCVD_IN_DNSWL_NONE,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of manuelandresr@aim.com designates 64.12.207.164 as permitted sender) Received: from [64.12.207.164] (HELO imr-mb01.mx.aol.com) (64.12.207.164) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 13 Dec 2011 14:38:26 +0000 Received: from mtaout-db01.r1000.mx.aol.com (mtaout-db01.r1000.mx.aol.com [172.29.51.193]) by imr-mb01.mx.aol.com (8.14.1/8.14.1) with ESMTP id pBDEc2fM006589 for ; Tue, 13 Dec 2011 09:38:02 -0500 Received: from [190.0.0.2] (unknown [190.26.254.201]) by mtaout-db01.r1000.mx.aol.com (MUA/Third Party Client Interface) with ESMTPA id C2250E00012A for ; Tue, 13 Dec 2011 09:38:01 -0500 (EST) Message-ID: <4EE76355.2060401@aim.com> Date: Tue, 13 Dec 2011 09:38:13 -0500 From: Manuel Andres Ramirez User-Agent: Mozilla/5.0 (X11; Linux i686; rv:8.0) Gecko/20111110 Thunderbird/8.0 MIME-Version: 1.0 To: ooo-users@incubator.apache.org Subject: Re: DDE linked cells fool the "=0" test, and dismay paintbrush References: <4EE56804.4050506@rochester.rr.com> In-Reply-To: <4EE56804.4050506@rochester.rr.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit x-aol-global-disposition: G DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mx.aol.com; s=20110426; t=1323787082; bh=rs+OKPyObfGx4b+lMySYiu6y8T2/hAjT2q8oY8rtjIo=; h=From:To:Subject:Message-ID:Date:MIME-Version:Content-Type; b=PgT1UJg0CW3OVVb8TAjM4M9QODYi74kJrKaWbL8GPd1W6rdvbtNmA4oWUFbrlwVeQ t2J1AAxms+QcD2yE76bKRXe8OJR0rFyrP4587hi5G1AFL7nWF2QQ7W5bwgddp85rsb xaROJ+rhYX+Wj+oB5QlYVK+QXU3Y1qjc73MzZjBg= X-AOL-SCOLL-SCORE: 0:2:335742720:93952408 X-AOL-SCOLL-URL_COUNT: 0 x-aol-sid: 3039ac1d33c14ee763497c9b X-AOL-IP: 190.26.254.201 El 11/12/11 21:33, Dave Babcock escribió: > OO 3.3.0, on Vista. > I have an array A1:Z55, populated by link from a different > spreadsheet. The cells there are all formatted to not show 0 in empty > cells (# format code). > Entries transfer correctly, etc, and math, text, all work well. > Except that if I do: > IF(A3=0;this;that) > > it always does the "that", /even if /the originating cell is empty or > has a value of zero. Thus, > IF(A3=0;"";57/A3) > will return a divide-by-zero error if A3=0, instead of giving a blank > cell. > > I have two workarounds: > -don't use the # in number format. Not acceptable for various reasons. > -add a transparent operation, viz: IF(A3=0;"";57/(A3*1)) This > works, but is hokey. > > Am I missing something? > > AND, this same block of linked data makes the format paintbrush go a > little crazy. Maybe it has to, by some odd quirt of the DDE process. > Cells can be formatted individually, but not by using the paintbrush. > > > Ol' Bab > Dave, Try use the function value(), like this IF(VALUE(A3)=0;"";57/A3). Maybe the if function don't recognize the value in A3, because it is inside a matriz DDE. Regards. -- Manuel Andrés Ramírez P. Administrador Informático GNU/Linux Registered User:412887 www.obtengalinux.org <> "El placer más noble es el júbilo de comprender" (Leonardo Da Vinci) --------------------------------------------------------------------- To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org For additional commands, e-mail: ooo-users-help@incubator.apache.org