Re: OpenOffice 3.3 VLOOKUP anomolie
Dave Babcock <[email protected]> Mon, 19 Dec 2011 20:23:25 -0500
| Newsgroups | gmane.comp.openoffice.questions |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format. --------------070509080106020903080105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit On 12/18/2011 3:02 PM, [email protected] wrote: > I have been using OO 3.2 with good success. But upon installing and > using OO 3.3 I am encountering serious problems. That which I know so > far is that OO3.3 Spreadsheet will not carry out calculations > wherin one or more values are obtained via a VLOOKUP.function > Specifically I have several spreadsheets that obtain values from > another spreadsheet. E.g. a specific code is: > =VLOOKUP($A16;'file:///C:/Documents and Settings/jreyr/My > Documents/ATTACHE-FINANCE/GJJT last.xls'#$Sheet1.$A$2:$M$27;2;FALSE()) > Numeric values are returned as text, e.g. '245, and calculations are > not carried out; Paste Special Numbers returns a blank cell. > Calculations are carried out OK with OO 3.2. > This situation with OO3.3 is unsatisfactory and is useless to me. Upon > installing OO3.3, OO 3.2 is gone. *Is there a remedy to the VLOOKUP > problem or is OO3.2 available?* The calculations are carried out > daily, so I am in real trouble with this and urgently await a reply. > s/G.G. Wire This looks similar, perhaps the same as, the DDE problem I asked about last week, where numeric data obtained this way fails a "=0" test. Since my symptom is that blank cells in the originating document fail the =0 test but other values work fine, my workaround is to substitute "(A3*1)" for "A3" wherever it matters. This seems to force the DDE encoding of a blank cell to be "truly" blank. And thus test as zero. I did discover that the "VALUE()" function does NOT help... I suspect this is not helpful, for I see your symptoms are much worse. Hmm. I AM using 3.3.1. Maybe a different build, say because I have Vista, you have ? Dave B -- ----------------------------------------------------------------- To unsubscribe send email to [email protected] For additional commands send email to [email protected] with Subject: help --------------070509080106020903080105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit <html> <head> <meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type"> </head> <body bgcolor="#FFFFFF" text="#000000"> On 12/18/2011 3:02 PM, <a class="moz-txt-link-abbreviated" href="mailto:[email protected]">[email protected]</a> wrote: <blockquote cite="mid:[email protected]" type="cite"> <meta content="text/html; charset=ISO-8859-1" http-equiv="Content-Type"> <meta name="GENERATOR" content="MSHTML 8.00.6001.19170"> <font id="role_document" color="#000000" face="Arial" size="2"> <div> <div>I have been using OO 3.2 with good success. But upon installing and using OO 3.3 I am encountering serious problems. That which I know so far is that OO3.3 Spreadsheet will not carry out calculations wherin one or more values are obtained via a VLOOKUP.function Specifically I have several spreadsheets that obtain values from another spreadsheet. E.g. a specific code is:</div> <div> </div> <div>=VLOOKUP($A16;'<a class="moz-txt-link-freetext" href="file:///C:/Documents">file:///C:/Documents</a> and Settings/jreyr/My Documents/ATTACHE-FINANCE/GJJT last.xls'#$Sheet1.$A$2:$M$27;2;FALSE())</div> <div> </div> <div>Numeric values are returned as text, e.g. '245, and calculations are not carried out; Paste Special Numbers returns a blank cell. Calculations are carried out OK with OO 3.2. </div> <div> </div> <div>This situation with OO3.3 is unsatisfactory and is useless to me. Upon installing OO3.3, OO 3.2 is gone. <strong>Is there a remedy to the VLOOKUP problem or is OO3.2 available?</strong> The calculations are carried out daily, so I am in real trouble with this and urgently await a reply.</div> <div> </div> <div>s/G.G. Wire</div> </div> </font> </blockquote> <big><big><font size="+1"><big><big><br> </big></big>This looks similar, perhaps the same as, the DDE problem I asked about last week, where numeric data obtained this way fails a "=0"</font></big> test. Since my symptom is that blank cells in the originating document fail the =0 test but other values work fine, my workaround is to substitute "(A3*1)" for "A3" wherever it matters. This seems to force the DDE encoding of a blank cell to be "truly" blank. And thus test as zero.<br> I did discover that the "VALUE()" function does NOT help...<br> <br> I suspect this is not helpful, for I see your symptoms are much worse. Hmm. I AM using 3.3.1. Maybe a different build, say because I have Vista, you have ?<br> <br> Dave B</big><br> </body> </html> --------------070509080106020903080105--