OO 2.4 - Possible Conversion Problem for VLOOKUP, SUMIF

[adnhk]

Hi Everyone,

I have a very complicated Excel spreadsheet (to me at least) that does not display any results in OO's Calc 2.4 (just a dash "-") while the same spreadsheet shows the results (in numbers) on Excel. Can some kind soul take a look at the VLOOKUP and SUMIF codes below to spot any possible errors in the conversion process?

Basically, the Excel spreadsheet has 3 worksheets, namely "Tech Infra & GPS", "working-TIGPS", and "raw file" (w/o quotes). Calc 2.4 renamed them to "Tech Infra _ GPS", "Working_TIGPS", and "raw file" (w/o quotes). Taking a sample VLOOKUP and SUMIF code from the same spreadsheet in the same cell,

Excel
=VLOOKUP($C13,'working-TIGPS'!$C$2:$AN$102,'Tech Infra & GPS'!$G$1+2,FALSE)

Calc
=VLOOKUP($C13;$working_TIGPS.$C$2:$AN$102;$'Tech Infra _ GPS'.$G$1+2;FALSE())

Excel
=SUMIF('raw file'!$AA:$AA,$C5&$A$1&$B$1&$C$1,'raw file'!BL:BL)

Calc
=SUMIF($'raw file'.$AA$1:$AA$65536;$C5&$A$1&$B$1&$C$1;$'raw file'.BL$1:BL$65536)

Can some experts help to check and advise if there is anything wrong with OO 2.4 conversion of these formulas? Thanks a million in advance.

[keme]

The conversion looks OK to me, except for one thing:
In the reference to the "Tech infra & GPS" sheet the spaces around "&" are kept in the Calc conversion. You didn't type the spaces in the explanation. That's probably just a typing glitch, and the names are correct in the spreadsheet. Even so, it's worth to check, as the & character is an operator, and might cause erratic behaviour when used in references (names).

[adnhk]

Thanks keme for the fast reply. You are right. It is a typo. I have edited my initial post to place in the spaces. Hmmm... if the conversion is right, I really wonder why Calc shows a dash "-" for all its results.

[keme]

Calc doesn't convert text formatted content to numerical values on the fly, as Excel does. That may cause differences like this.

It's hard to say more without knowing details of what we're talking about. Could you show the content of cells/ranges referenced (at best attach a copy of your file), or at least indicate the nature of it if the actual content is confidential.

[adnhk]

Hi keme,

Yes, I understand the difficulty. I have randomly deleted most of the content of the spreadsheet, left with only Comp - Depre - Software and Comp Software Related Expenses with some numbers as an example. Can you kindly take a look and see if you can spot the issue? Thanks a million in advance.

[keme]

In Calc, comparing is done either verbatim or through regular expressions. In either situation, the simple wildcards from Excel, ? and * , will not work as expected. They are either treated as search terms (i.e. they will only match question mark and asterisk, respectively), or refer to the immediately preceding token (character or group). To make it work in Calc, you need to...

• Enable the use of regular expressions within formulas
  - Select menu item Tools - Options
  - Expand branch OpenOffice.org Calc - Calculate
  - Tick Enable regular expressions in formulas
• Make the * wildcard match any number of arbitrary characters
  The period character is the "match any single character" symbol in regular expressions, and the asterisk means "match any number of the previous character", so ".*" will work like "*" does in Excel
  Change "Te*" to "Te.*" in the formula in Working_TIGPS.A1 (and possibly elsewhere, I didn't check thoroughly...)

This difference is not caught by the conversion, mainly because the asterisks are character constants. Calc will convert operators, functions and references as necessary, but leaves given literal values untouched.

[Villeroy]

Workaround for "*" and "?"
Create a set of named references (Ctrl+F3)
shSep =MID(ADDRESS(1;1;1;"Foo");4;1)
[Add], [OK]
Restart the dialog (Ctrl+F3) before you add names depending on newly added "shSep":
Joker =IF(shSep=".";".";"")&"*"
[Add]
anyChar =IF(shSep=".";".";"?")
[Add]
[OK]
Now shSep, Joker and anyChar refer to different strings in Excel and Calc respectively.
=MATCH("A"&Joker&"B";range;0) will match pattern "A?B" in Excel and regex "A.B" in Calc.

Edit: As keme mentioned, this is a way to make a xls-document with patterns Calc compatible. I should add the fact that all xls gets imported with support of regular expressions turned off. It is better to show errors than wrong results. When you applied my small "patch" throughout your xls-document and load the stuff in Calc, it is required then to turn on regex support.

[keme]

Note that Villeroy's approach adds an abstraction level of sorts, making his the most general and compatible one. If you will be using the spreadsheets on both Excel and Calc, you must go that route.

Only if you are migrating to OOo, and that spreadsheet will not need to be opened in Excel again, you may use the simpler approach I suggested.

[adnhk]

Thank you both, keme and Villeroy for all your help.

Hi keme, your quick fix does work but I do find turning ON regular expressions slows down the calculations a lot on the original spreadsheet of 4.5MB. There may be others, other than Te* but I have to really go dig the entire spreadsheets. Apart from * and ?, are there any other ones that I should look out for? All I needed to do is to add in a . in front of all these I suppose?

Hi Villeroy,

Apologies for my ignorance, cos I am not really good in Excel formulas. Can you help to use my test spreadsheet to add those that you had mentioned as an example of how it works? I turn OFF regular expressions, take away the "." from Te, then I tried to follow your advise, by adding in the following into the test spreadsheet which I open with Calc:

Name: shSep
Assiged to: MID(ADDRESS(1;1;1;"Foo");4;1)
[Add], [OK]

Name: Joker
Assigned to: IF(shSep=".";".";"")&"*"
[Add]

Name: anyChar
Asigned to: IF(shSep=".";".";"?")
[Add]
[OK]

Press F9 to refresh calculations, but still turns out with no results. Just a dash "-" (w/o quotes).

I then use Excel to follow the above, Excel seems to automatically add in an = sign in front of e.g., IF and MID, making them =IF and =MID. Save the spreadsheet, open in Calc, press CTRl F3, saw that Calc now adds an extra quotation for each. E.g., Assinged To: "IF(shSep=".";".";"?")" but of course, still no results.

Once again, apologies for my ignorance. I am not an Excel expert, and hence my questions and queries may sound really stupid...

If I understand both correctly, adding in these 3 Named references (assuming I've done it correctly) should make the most, if not all spreadsheets that has VLOOKUP AND SUMIF works in both Excel and Calc?

[Villeroy]

The leading "=" in the named reference should make no difference. In your sheet formulas you need to replace "*" with "anyChar" and "?" with "Joker".
Test the names with formulas
=shSep
=anyChar
=Joker
anywhere in your document.

This method to enable pattern matching in both applications is far from perfect. Notice the comment I added to my previous post.

[adnhk]

Hi Villeroy,

Though I still do not fully understand your advise on the above post, can I assume these named reference have to be input manually into all spreadsheets that has * and ? and wildcards? Can you perhaps help to put in these named reference into the test spreadsheet that I had posted above so I can take a look at how you make it work? I actually tried to add in these references and since my test spreadsheet only have 1 * to be referenced, I am still unable to make it work following your advise. Sincerely hope you can help to show me using the sample spreadsheet that I had posted.

On a side note, can you or anyone advise what is the best way to make 200 spreadsheets with such VLOOKUP and SUMIF be compatible with both Excel and Calc?

Thanks to all for your help.

[Villeroy]

working_tigps.A1 =IF($'Tech Infra _ GPS'.A3="All";"Te"&anyChar;$'Tech Infra _ GPS'.A3)

[adnhk]

Thanks Villeroy for your tip. Now I think I understand more about Named Ref better. However, if you take a look at the test spreadsheet, on the first sheet, there is a pull down selection list with various countries to choose from, in Excel, choosing some of these countries have a result in one of those 2, Comp - Depre - Software or Comp Software Related Expenses. I was having the thoughts that keme's simple solution of adding in a * does not work for those selection list, but seems like using Named Ref also does not show anything when I change to another country in Calc. Can there be any other incompatibilities on selection list between Calc and Excel that I need to look out for? Sincerely apologise for the trouble again. Thanks a million in advance.

[adnhk]

Hi Villeroy,

Are you able to advise on my query with regards to drop down list import from Excel? I tried to troubleshoot this issue the whole day through but to no avail

[Villeroy]

No, I'm not able to do your work, particulary not for the 200+ files you mentioned. Looking at your file, I have another hint: Stop that spreadsheet madness. It is by far too dangerous. For what this file obviously tries to do, a relational database would be a far better storage-medium. (No, not taking about OOo Base. However, Base could be one frontend of a decent database backend, passing over data to Writer and Calc).
Some lecture and starting point for browsing: http://www.eusprig.org/

[adnhk]

Hi Villeroy,

I think you may have mistaken my meaning. I am not asking anyone to do my work. I am asking for help to sort out these issues I am currently facing, so that once sorted out, I can then do it for the rest of the depts in the enterprise I am working for. How can OO be successful in a business world with 90% of enterprises using MS Office stuff, and when people are trying to port over these MS Office documents to OO, only to face more issues and not able to get any help?

Migrating hundreds of documents from one format to another WILL cost any company lots of resources and time. Hence, I am currently taking the initiative to try my best and help to migrate for all these depts. Or am I asking too much from the OO community?

Hence please understand my point of views and where I am coming from. It is already difficult enough in even getting anyone from the enterprise to move over from MSO to OO, not to mention even asking anyone to try and port over their "BELOVED" MS OFFICE documents themselves over to OO...

Hence, I would truly and sincerely like to ask for your help, or anyone who knows about drop down list in Calc, to take a look at the test spreadsheet. tapping on the drop down list to choose any other countries does not yield any results in OO, but in Excel, this works fine. Hence is there anything about OO and Excel that I should look out for in terms of compatibility in pull down/drop down list? Thanks a million in advance.

[adnhk]

Hi Everyone,

After these past few days, there is still no answers available to my query. But can anyone advise if this is a shortfall or a bug in Calc's implementation of Data Validity? And where and who can I contact to report this shortfall or bug?

[Villeroy]

=IF($'Tech Infra _ GPS'.A3="All";"Te"&anyChar;$'Tech Infra _ GPS'.A3)
=IF($'Tech Infra _ GPS'.A4="All";"*";$'Tech Infra _ GPS'.A4)
=IF($'Tech Infra _ GPS'.A5="All";"*";$'Tech Infra _ GPS'.A5)

You did not replace all occurences "*" with named reference anyChar. As already mentioned in the other thread you've got to turn on regular expressions in formulas explicitly for Excel files, since Calc assumes that Excel has no regexes. Then enforce a complete recalculation (Ctrl+Shift+F9).

[adnhk]

Thanks Villeroy for your kind help. However, I tried replacing these with a few combinations but still the results are not showing correctly and some combinations even give me Error codes. For e.g., I replaced

=IF($'Tech Infra _ GPS'.A4="All";"*";$'Tech Infra _ GPS'.A4)

with

=IF($'Tech Infra _ GPS'.A4="All";&anyChar;$'Tech Infra _ GPS'.A4)

and receive Err:510. The same err:510 is shown for =IF($'Tech Infra _ GPS'.A5="All";"*";$'Tech Infra _ GPS'.A5).

Using =IF($'Tech Infra _ GPS'.A4="All";""&anyChar;$'Tech Infra _ GPS'.A4) = No results at all even for ALL from the pull down menu.

Using =IF($'Tech Infra _ GPS'.A4="All";"*"&anyChar;$'Tech Infra _ GPS'.A4) = Showing the same results as ALL for any other countries in the pull down menu that I select, which is not correct too. Am I doing anything wrong?

On a side note, how does Calc convert dates from Excel spreadsheets? From the Pull Down list, if I change to Feb-08, I see it changes to the number 39486 instead of Feb-08. This is really strange.

[Villeroy]

See attached file in ODF format (regular expressions=ON). Just like your original Excel version this one uses text values for the dates in Y3:Y14.
"&anyChar" is meaningless (&-operator without operand)

[adnhk]

Hi Villeroy, thanks for your help in this. Sincerely appreciated all your efforts in helping to solve this issue. However, if you open the .ods file, you will notice that there are some results Under Tech Infra_GPS Sheet. Results are shown in:

Column G40, H40
Column G43, H43

Now, if you go to Column A3 of the same Tech Infra_GPS Sheet, pull down the menu (default is All) and select:
TECH INFRA & GPS (China) or
TECH INFRA & GPS (HO)

You will realise that Column G40, H40 and Column G43, H43 yields no result, just a dash "-", which is not the case in Excel. In Excel these 2 columns does have results, which is correct since the results are derived from the raw file Sheet.

That is why I am wondering if the Drop Down List of A3, A4 and A5 (I believe these should be using Data Validity right?) are not working correctly? Or am I missing something?

After reading one of your replies in another forum about date format in OO, http://www.oooforum.org/forum/viewtopic.phtml?t=69086 and also as per your advise here, I finally noticed that Tech Infra_GPS Sheet Y3:Y14 are in text values. I did a quick check using Excel 2003 on the original test xls file that I had posted earlier, and confirm these are already set as Date format (right click any cell in Y3:Y14, select Format Cells...) and somehow Calc imported them as text format. Anyway, in Calc, I explicitly format Y3:Y14 to be in Date format, set as user defined MMM-YY (please see attached date_format.jpg for more info), same format as the original test xls file. Then I go to Column D1, perform the same format cell, date settings. Now the strange thing happens:

Pull down D1 to select, say Feb-08, the column D6 changes to "Sum of 39486" instead of "Sum of Feb-08"... Please see attached date_format1.jpg for more info.

A check on the formula bar reads the date 02/08/2008? I tried changing this manually to Feb-08 and immediately gets a 'Invalid Value' message. Please see attached date_format2.jpg.

Apologies for the long post but sincerely hope you can advise these issues. Thanks a million in advance.

[Villeroy]

Concentrate on the contents and the formulas.
=VLOOKUP($C40;$working_TIGPS.$C$2:$AN$102;$'Tech Infra _ GPS'.$G$1+26;FALSE())
Since 'Tech Infra _ GPS'.$G$1 is empty the formula always refers to column AB (column #26 in working_TIGPS.$C$2:$AN$102). All there is are formatted zeros displayed as "-". If Excel returns anything else, then there must be a serious bug in Excel.
I'll backup this file as a living example of spreadsheet abuse.

[adnhk]

Hi Villeroy,

If you take a look at the .ods file, when Tech Infra_GPS's A3 - A5 are all under "All", there really is a value at G40, which is 2450. Since this column is asking for results of Comp - Depre - Software for Jan-08, this value is correctly derived from the "raw file" sheet's Sum of AN3 - AN6.

If you pull down Tech Infra_GPS's column A3 and select, say TECH INFRA & GPS (HO), TECH INFRA & GPS (HO) is inside "raw file" sheet's column AD, and since TECH INFRA & GPS (HO) refers to AD2 - AD4, but we wanted only for Comp - Depre - Software, then the result should be 1200 (AN3 + AN4), which is displayed correctly in Excel but a "-" in Calc.

For easy reference, I am attaching a screen shot of the raw_file's figures.

[Villeroy]

IMHO, the whole thing is completely broken by design even if it happens to work in Excel. If a simple database is not an option, this spreadsheet model is an unmaintainable, error-prone mess.

[adnhk]

Hi Villeroy,

Yes, I fully understand your point of views. However, sigh... as I had earlier mentioned, these spreadsheets belongs to the users, and I am trying to help them in making this spreadsheet work in OO's Calc. I do not have to power nor rights to force these users into changing the way they do their work

Can you advise if there is any way to make the Pull Down menu (Data Validity?) work the way it should, i.e., by selecting a department name, the results will be derived from "raw file" sheet correctly, and not just a dash "-"? By pulling down the menu, and select TECH INFRA & GPS (HO), Calc should automatically go to 'raw file" sheet and look for the data from columns AD and AN for Jan-08, etc? Also, is there a way to make the date in this spreadsheet work as intended too? Thanks.

[Villeroy]

Next round of trash-recycling.
The attached file does not use any pattern matching at all when loaded in Calc. In Excel it should use "*" (alias named reference "Joker").

When regexes in formulas become complete nonsense:

Code: Select all

a(b)c	2	=MATCH($A$1;$A$1:$A$2;0) always returns 2
abc		

The whole document is a testimonial of both spreadsheet abuse and spreadsheet ignorance. Calc makes things even worse.