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)&" "&TRIM(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