At 16:57 08/02/2014 +0200, Hylton Conacher wrote:
What I seek is to be able to have a sheet of Lottery results
downloaded from the Internet and pasted into a Calc sheet. On
another Calc sheet I will have a listing of the 6 numbers held for
each specific draw date. Each populated cell of my ticket sheet
will indicate if the number in that cell is the same as a number in
a range of cells from the results sheet, on a particular date.
I'm guessing (since you don't say) that the ticket sheet is not the
As an example:
Assume the draw results below dated drew the following numbers:
04/02/2014 16, 03, 19, 21, 23, 06
11/02/2014 17, 05, 02, 20, 22, 04
18/02/2014 09, 40, 05, 09, 42, 03
Also assume that draw numbers I chose for a draw on a date were:
11/02/2014 02, 05, 18, 20, 22, 04
If any number on my ticket matches a corresponding date value, I
want the cell to change background colour. After looking into the
usage of vlookup and given I want the background colour to change if
the conditions are met, I selected the cell containing the first
number on my ticket (02) and then selected 'Format', Conditional
Formatting'. I selected that the cell value is equal to and entered
vlookup($LottoResults.$B$39,$LottoResults.$C$39:$D$39,1,0) into the
field, chose the new background style and said OK.
I think you are getting confused here: are you trying to search two
things with one test? You presumably need to search the various date
rows for the appropriate date, and for this VLOOKUP() might be
appropriate. But you haven't attempted that here: you have mentioned
only cells in row 39. Yes, you may then also need to search the row
for a matching draw result, but that is along a row, not down a
column, so HLOOKUP() might be more appropriate for that. Don't you need both?
You have also said that the ticket numbers are on a separate sheet
from the draw numbers, but your search is confined to the results
sheet. You appear to be searching to see if a draw result happens to
be equal to the date on which it was drawn! That would happen on 11
February 2014 only if a number drawn were 41681! Now I can't speak
for South African lotteries, but ...
Using the other option on Conditional Formatting I selected 'Formula
is' and entered the same vlookup formula with the same disastrous results, ...
That tests the formula for zero result. How could your retrieved
draw result be zero? Now if you subtracted your ticket number from
the draw result ...
So close yet so far, what am I missing?
The need to apply two searches or tests. Oh, and the need to specify
I trust this helps.
To unsubscribe e-mail to: email@example.com
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
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy