Re: [libreoffice-users] calc: vlookup not behaving in one spreadsheet

[Gary Dale]

Thanks. That explains everything.

On 06/10/17 08:23 PM, Remy Gauthier wrote:

Hi,

I think your issue comes from the changes introduced in Version 
5.3. The V5.3 release notes 
(https://wiki.documentfoundation.org/ReleaseNotes/5.3#Calc) say the 
following:



  Option settings

  * In new installations the default setting for new documents is now
*Enable wildcards in formulas* instead of regular expressions.
tdf#88581
 (Eike
Rathke (Red Hat, Inc.))
  o This is for better interoperability with other spreadsheet
applications and a better experience for users who are not
familiar with regular expressions. Wildcards are more
widespread and the large set of regular expression
metacharacters often makes queries too complicated for casual
users.
  o See also ReleaseNotes 5.2 for wildcards

.


If you go to Tools -> Options -> Calc -> Calculate and enable "Enable 
regular expressions in formulas", it should work (at least, it does 
for me). You can also change your ".*" to just "*" to make it work 
(but that will prevent the other spreadsheet from perhaps working 
correctly).


If you want to be more robust, you could use a dedicated worksheet 
that has a small array of lets say 1 columns and 6 rows. In column 1 
(starting at A1), you place a, b, c, d a, e, f and in column 2 
(starting at B1), you place 1, 2, 3, 4, 5, 6, like this:


a 1
b 2
c 3
d a 4
e 5
f 6

Create a named cell (for instance "Wildcard" without the quotes) that 
contains this formula:


=IF(ISERROR(VLOOKUP("d"&"*",A1:B6,2,0)),".*","*")

You can then use something like this as your search element the lookup 
function: UPPER(TRIM(C2)&" "(E2)


I tried to use an inline matrix instead of A1:B6 but I did not get any 
good results. If anyone has an idea, I would be interested in knowing 
how to do it (it would require only one cell instead of 7 (6 for the 
array, 1 for the result)).


I hope this helps.

Rémy Gauthier.

Le vendredi 06 octobre 2017 à 18:46 -0400, Gary Dale a écrit :

I have two spreadsheets. I created the first back in June. It uses two
sheets, one for registration information (Entrants) which I download
from a site that takes registrations for races, and the other (Results)
I download from a site that the timer posts results to. The second sheet
has the standing information while the first has all the entrant
information. I can relate the two by the entrant's name, which is 3
columns in the first sheet and 1 (uppercase) column in the second using
the formula:

=N(VLOOKUP(UPPER(TRIM(C2)&" "(E2)&".*"),Standings.C$2:J$37,8,0))

where C & E are the first and last name columns on Entrants, Standings.C
contains the combines first and last names and column J is the number I
want (standing within a division by age and gender). A failed lookup
would mean the person registered but didn't complete the event.

This works in the spreadsheet I created in June but doesn't work in the
spreadsheet I just created. I traced this down to the &".*" part of the
search term. This was intended to handle trailing spaces or other
extraneous characters after the person's name. The spreadsheet I created
in June didn't have any but the one I just created did. However removing
the trailing spaces from the Standings names didn't help. The formula
simply didn't work.

Replacing the &".*" with a simple &" " to assume that the names always
have a trailing space did work, as did removing the trailing spaces and
leaving out the &" ". However the original formula works on the June
spreadsheet with or without trailing spaces in the names.

I'm working with both spreadsheets on the same computer using the same
copy of LibreOffice Calc (5.4.1.2.0+ on a Debian Buster AMD64 system). I
can have them open side by side to demonstrate the problem (it's also
how I copied and pasted the formula between the two - multiple times -
to ensure that it wasn't a typing error).

Does anyone have any ideas on what could be going wrong?





--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] calc: vlookup not behaving in one spreadsheet

[Brian Barker]

At 18:46 06/10/2017 -0400, Gary Dale wrote:
I have two spreadsheets. I created the first back in June. It uses 
two sheets, ...
I can relate the two by the entrant's name, which is 3 columns in 
the first sheet and 1 (uppercase) column in the second using the formula:


=N(VLOOKUP(UPPER(TRIM(C2)&" "(E2)&".*"),Standings.C$2:J$37,8,0))

This works in the spreadsheet I created in June but doesn't work in 
the spreadsheet I just created. I traced this down to the &".*" part 
of the search term. [...]
Replacing the &".*" with a simple &" " to assume that the names 
always have a trailing space did work, as did removing the trailing 
spaces and leaving out the &" ". However the original formula works 
on the June spreadsheet with or without trailing spaces in the names.


Does anyone have any ideas on what could be going wrong?


You are relying on VLOOKUP() supporting regular expressions. For this 
to be so, you must have a tick at Tools | Options... | LibreOffice 
Calc | Calculate | Enable regular expressions in formulae. It seems 
that you have this ticked in your June spreadsheet but not in your 
newer one. Note that, although this appears to be a setting in the 
application, it is actually saved in each document. With the tick in 
place, ".*" means zero or more of any character (which is what you 
mean), whereas with no tick it means just the two characters dot-asterisk.


I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] calc: vlookup not behaving in one spreadsheet

[Remy Gauthier]

Hi,
I think your issue comes from the changes introduced in Version
5.3. The V5.3 release notes (https://wiki.documentfoundation.org/Releas
eNotes/5.3#Calc) say the following:
Option settings
In new installations the default setting for new documents is now
Enable wildcards in formulas instead of regular expressions. tdf#88581
(Eike Rathke (Red Hat, Inc.))

This is for better interoperability with other spreadsheet applications
and a better experience for users who are not familiar with  regular
expressions. Wildcards are more widespread and the large set
of  regular expression metacharacters often makes queries too
complicated for casual users.See also ReleaseNotes 5.2 for wildcards.
If you go to Tools -> Options -> Calc -> Calculate and enable "Enable
regular expressions in formulas", it should work (at least, it does for
me). You can also change your ".*" to just "*" to make it work (but
that will prevent the other spreadsheet from perhaps working
correctly).
If you want to be more robust, you could use a dedicated worksheet that
has a small array of lets say 1 columns and 6 rows. In column 1
(starting at A1), you place a, b, c, d a, e, f and in column 2
(starting at B1), you place 1, 2, 3, 4, 5, 6, like this:
a   1b   2c   3d a 4e   5f   6
Create a named cell (for instance "Wildcard" without the quotes) that
contains this formula:
=IF(ISERROR(VLOOKUP("d"&"*",A1:B6,2,0)),".*","*")
You can then use something like this as your search element the lookup
function: UPPER(TRIM(C2)&" "(E2)
I tried to use an inline matrix instead of A1:B6 but I did not get any
good results. If anyone has an idea, I would be interested in knowing
how to do it (it would require only one cell instead of 7 (6 for the
array, 1 for the result)).
I hope this helps.
Rémy Gauthier.
Le vendredi 06 octobre 2017 à 18:46 -0400, Gary Dale a écrit :
> I have two spreadsheets. I created the first back in June. It uses
> two 
> sheets, one for registration information (Entrants) which I download 
> from a site that takes registrations for races, and the other
> (Results) 
> I download from a site that the timer posts results to. The second
> sheet 
> has the standing information while the first has all the entrant 
> information. I can relate the two by the entrant's name, which is 3 
> columns in the first sheet and 1 (uppercase) column in the second
> using 
> the formula:
> 
> =N(VLOOKUP(UPPER(TRIM(C2)&" "(E2)&".*"),Standings.C$2:J$37,8,0))
> 
> where C & E are the first and last name columns on Entrants,
> Standings.C 
> contains the combines first and last names and column J is the number
> I 
> want (standing within a division by age and gender). A failed lookup 
> would mean the person registered but didn't complete the event.
> 
> This works in the spreadsheet I created in June but doesn't work in
> the 
> spreadsheet I just created. I traced this down to the &".*" part of
> the 
> search term. This was intended to handle trailing spaces or other 
> extraneous characters after the person's name. The spreadsheet I
> created 
> in June didn't have any but the one I just created did. However
> removing 
> the trailing spaces from the Standings names didn't help. The
> formula 
> simply didn't work.
> 
> Replacing the &".*" with a simple &" " to assume that the names
> always 
> have a trailing space did work, as did removing the trailing spaces
> and 
> leaving out the &" ". However the original formula works on the June 
> spreadsheet with or without trailing spaces in the names.
> 
> I'm working with both spreadsheets on the same computer using the
> same 
> copy of LibreOffice Calc (5.4.1.2.0+ on a Debian Buster AMD64
> system). I 
> can have them open side by side to demonstrate the problem (it's
> also 
> how I copied and pasted the formula between the two - multiple times
> - 
> to ensure that it wasn't a typing error).
> 
> Does anyone have any ideas on what could be going wrong?
> 
> 
-- 
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

[libreoffice-users] calc: vlookup not behaving in one spreadsheet

[Gary Dale]

I have two spreadsheets. I created the first back in June. It uses two 
sheets, one for registration information (Entrants) which I download 
from a site that takes registrations for races, and the other (Results) 
I download from a site that the timer posts results to. The second sheet 
has the standing information while the first has all the entrant 
information. I can relate the two by the entrant's name, which is 3 
columns in the first sheet and 1 (uppercase) column in the second using 
the formula:


=N(VLOOKUP(UPPER(TRIM(C2)&" "(E2)&".*"),Standings.C$2:J$37,8,0))

where C & E are the first and last name columns on Entrants, Standings.C 
contains the combines first and last names and column J is the number I 
want (standing within a division by age and gender). A failed lookup 
would mean the person registered but didn't complete the event.


This works in the spreadsheet I created in June but doesn't work in the 
spreadsheet I just created. I traced this down to the &".*" part of the 
search term. This was intended to handle trailing spaces or other 
extraneous characters after the person's name. The spreadsheet I created 
in June didn't have any but the one I just created did. However removing 
the trailing spaces from the Standings names didn't help. The formula 
simply didn't work.


Replacing the &".*" with a simple &" " to assume that the names always 
have a trailing space did work, as did removing the trailing spaces and 
leaving out the &" ". However the original formula works on the June 
spreadsheet with or without trailing spaces in the names.


I'm working with both spreadsheets on the same computer using the same 
copy of LibreOffice Calc (5.4.1.2.0+ on a Debian Buster AMD64 system). I 
can have them open side by side to demonstrate the problem (it's also 
how I copied and pasted the formula between the two - multiple times - 
to ensure that it wasn't a typing error).


Does anyone have any ideas on what could be going wrong?


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] system sound + Libreoffice [SOLVED]

[Thomas Blasejewicz]

On 2017/10/06 23:00, Tim-L wrote:


Last time I heard of this type of problem was in the early Windows 
days [i.e. Windows 95 era?].  ASCII has a non-printable character that 
actually causes the beep noise with the motherboard hardware.


On 10/6/2017 5:11 AM, Thomas Blasejewicz wrote:

Good evening
I noted a very strange behavior.
Although I have set Windows (10) system sounds to "no sound",
in more or less frequent intervals the systems starts beeping (e.g., 
when switching to Caps Lock).


This happens ***ONLY*** when I am using Libreoffice.
It used to go away, when I reset the "no sound".
By now, this does not work any longer. The computer annoyingly keeps 
"beeping me crazy".


Is there a trick to stop that behavior???

Thank you
Thomas









Hopefully I have "solved" the problem
https://www.howtogeek.com/252226/how-to-make-windows-play-a-sound-when-you-press-caps-lock-num-lock-or-scroll-lock/

Since the computer did not act so before, I have no idea how this came 
to pass, but there is a KEYBOARD setting

designed to make sounds when toggling keys.
I turned that OFF ... and now the computer finally keeps quiet - VERY 
helpful when working at 2 am (now!)


Thank you
Thomas

--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] system sound + Libreoffice

[Tim-L]

Last time I heard of this type of problem was in the early Windows days 
[i.e. Windows 95 era?].  ASCII has a non-printable character that 
actually causes the beep noise with the motherboard hardware.


The question is whether the "no sound" is for the sound card, the MoB's 
"beeper", or both.  Even if the "no sound" stops the hardware beeping, I 
would bet that LibreOffice might flip the "flag" used by the "no sound" 
option on the motherboard and not the sound card.


Those early days of PCs. PC-ATs, PC-XT, etc. did not have a sound card 
on the MoB.  You had to buy one that work with your PC Bus slot.  So 
most developers used the "beeper" character instead of using an optional 
sound card.


SO, there may be something withing the oldest surviving code that may 
mess with the "flag" settings dealing with sound.  I remember something 
about the early days with StarOffice[?] running on DOS


On 10/06/2017 09:09 AM, Paul D. Mirowsky wrote:

Do a memory check immediately.

See 
https://lifehacker.com/5531900/use-an-ubuntu-live-cd-to-test-your-pcs-memory


Also try Writer with the live boot CD if it occurs under linux.

Hope this helps.


On 10/6/2017 5:11 AM, Thomas Blasejewicz wrote:

Good evening
I noted a very strange behavior.
Although I have set Windows (10) system sounds to "no sound",
in more or less frequent intervals the systems starts beeping (e.g., 
when switching to Caps Lock).


This happens ***ONLY*** when I am using Libreoffice.
It used to go away, when I reset the "no sound".
By now, this does not work any longer. The computer annoyingly keeps 
"beeping me crazy".


Is there a trick to stop that behavior???

Thank you
Thomas








--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

Re: [libreoffice-users] system sound + Libreoffice

[Paul D. Mirowsky]

Do a memory check immediately.

See  
https://lifehacker.com/5531900/use-an-ubuntu-live-cd-to-test-your-pcs-memory


Also try Writer with the live boot CD if it occurs under linux.

Hope this helps.


On 10/6/2017 5:11 AM, Thomas Blasejewicz wrote:

Good evening
I noted a very strange behavior.
Although I have set Windows (10) system sounds to "no sound",
in more or less frequent intervals the systems starts beeping (e.g.,  
when switching to Caps Lock).


This happens ***ONLY*** when I am using Libreoffice.
It used to go away, when I reset the "no sound".
By now, this does not work any longer. The computer annoyingly keeps  
"beeping me crazy".


Is there a trick to stop that behavior???

Thank you
Thomas





--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted

[libreoffice-users] system sound + Libreoffice

[Thomas Blasejewicz]

Good evening
I noted a very strange behavior.
Although I have set Windows (10) system sounds to "no sound",
in more or less frequent intervals the systems starts beeping (e.g., 
when switching to Caps Lock).


This happens ***ONLY*** when I am using Libreoffice.
It used to go away, when I reset the "no sound".
By now, this does not work any longer. The computer annoyingly keeps 
"beeping me crazy".


Is there a trick to stop that behavior???

Thank you
Thomas


--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted