Hi,
I am looking for a pointer or two to enable me to accomplish what I seek.
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.
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
In theory looking at the data, if I submitted my ticket for prize
redemption, I might score very well considering my ticket matches 5 out
of 6 of the numbers from the draw. If any number on my ticket matches a
corresponding date value, I want the cell to change background colour.
Now the trick is to put this in practice. :)
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 checked the first number of my ticket on the specified date and
noticed that the background had not changed as instructed to by the
conditional formatting. :(
Using the other option on Conditional Formatting I selected 'Formula is'
and entered the same vlookup formula wit the same disastrous results,
i.e. no colour change on the first cell of my ticket number :(
Doing a second trial I said cell value must equal and entered
$LottoResults.$C$39.
The cell on my ticket came out the requested colour however the formula
did not allow me to check if on the ticket date and corresponding ticket
draw were identical. :(
The purchased ticket has 6 numbers on it, with the first 5 being in
numerical order and the 6th being a bonus random ball( 01-49).
The draw results are not in numerical order.
So close yet so far, what am I missing?
pointers?
Regards
Hylton
--
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