Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default conditional formatting

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default conditional formatting

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH


"Lofty" wrote:

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default conditional formatting

..... SUM Condition is not UNIQUE! (Dooh!) so I'll give some more thought!

Sorry!

"Toppers" wrote:

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH


"Lofty" wrote:

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default conditional formatting

Thanks
It works to a point except when you put the following week's numbers in
"this week's numbers" it unformats so the "numbers picked so far" or the
numbers in the rows do not stay red/white. It's given me a start anyway so
I'll see what I can develop.

"Toppers" wrote:

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH


"Lofty" wrote:

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default conditional formatting

For matching a line try:

Formula is:

=AND(MATCH(B1,Lotto,0),MATCH(C1,Lotto,0),MATCH(D1, Lotto,0),MATCH(E1,Lotto,0),MATCH(F1,Lotto,0),MATCH (G1,Lotto,0))

HTH

"Lofty" wrote:

Thanks
It works to a point except when you put the following week's numbers in
"this week's numbers" it unformats so the "numbers picked so far" or the
numbers in the rows do not stay red/white. It's given me a start anyway so
I'll see what I can develop.

"Toppers" wrote:

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH


"Lofty" wrote:

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default conditional formatting

Hi
I've cracked it...all you do is put the numbers drawn each week in a row to
one side. then -as you said- highlight a row of numbers against a person's
name and the conditional format using:-
=Match(B1,draw,0) where 'draw' is the row of cells (i used 50 cells as
there are only 49 numbers).
For the "Numbers drawn" square - again use the same formula.
You then use 'Format Painter' and apply to all the numbers in the rows.
Works a treat.
All I wanted was a kick and it got what I wanted,,,Thank You Very Much...

Lofty

"Toppers" wrote:

For matching a line try:

Formula is:

=AND(MATCH(B1,Lotto,0),MATCH(C1,Lotto,0),MATCH(D1, Lotto,0),MATCH(E1,Lotto,0),MATCH(F1,Lotto,0),MATCH (G1,Lotto,0))

HTH

"Lofty" wrote:

Thanks
It works to a point except when you put the following week's numbers in
"this week's numbers" it unformats so the "numbers picked so far" or the
numbers in the rows do not stay red/white. It's given me a start anyway so
I'll see what I can develop.

"Toppers" wrote:

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH


"Lofty" wrote:

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,339
Default conditional formatting

Glad it's all working. If YOU win, remember me!

"Lofty" wrote:

Hi
I've cracked it...all you do is put the numbers drawn each week in a row to
one side. then -as you said- highlight a row of numbers against a person's
name and the conditional format using:-
=Match(B1,draw,0) where 'draw' is the row of cells (i used 50 cells as
there are only 49 numbers).
For the "Numbers drawn" square - again use the same formula.
You then use 'Format Painter' and apply to all the numbers in the rows.
Works a treat.
All I wanted was a kick and it got what I wanted,,,Thank You Very Much...

Lofty

"Toppers" wrote:

For matching a line try:

Formula is:

=AND(MATCH(B1,Lotto,0),MATCH(C1,Lotto,0),MATCH(D1, Lotto,0),MATCH(E1,Lotto,0),MATCH(F1,Lotto,0),MATCH (G1,Lotto,0))

HTH

"Lofty" wrote:

Thanks
It works to a point except when you put the following week's numbers in
"this week's numbers" it unformats so the "numbers picked so far" or the
numbers in the rows do not stay red/white. It's given me a start anyway so
I'll see what I can develop.

"Toppers" wrote:

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH


"Lofty" wrote:

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8
Default conditional formatting

Sorry - but as the runner I'm not allowed to enter!......But if you're on
Anglesey come to Y Bedol

"Toppers" wrote:

Glad it's all working. If YOU win, remember me!

"Lofty" wrote:

Hi
I've cracked it...all you do is put the numbers drawn each week in a row to
one side. then -as you said- highlight a row of numbers against a person's
name and the conditional format using:-
=Match(B1,draw,0) where 'draw' is the row of cells (i used 50 cells as
there are only 49 numbers).
For the "Numbers drawn" square - again use the same formula.
You then use 'Format Painter' and apply to all the numbers in the rows.
Works a treat.
All I wanted was a kick and it got what I wanted,,,Thank You Very Much...

Lofty

"Toppers" wrote:

For matching a line try:

Formula is:

=AND(MATCH(B1,Lotto,0),MATCH(C1,Lotto,0),MATCH(D1, Lotto,0),MATCH(E1,Lotto,0),MATCH(F1,Lotto,0),MATCH (G1,Lotto,0))

HTH

"Lofty" wrote:

Thanks
It works to a point except when you put the following week's numbers in
"this week's numbers" it unformats so the "numbers picked so far" or the
numbers in the rows do not stay red/white. It's given me a start anyway so
I'll see what I can develop.

"Toppers" wrote:

Lofty,
Did you really mean the numbers are rows rather than columns?
Or should numbers be in columns with a row per name?

Anyway, for columns of lotto numbers (Col A is name, B to G are numbers):

For each column : Formula is: = Match(B1,Lotto,0) where "Lotto" is a named
range the 6 numbers for the draw. Set format as required.

Repeat for columns C to G i.e Match(C1,Lotto,0) etc

With names in column A set CF:

Formula is: =Sum(B1:G1)=Sum(Lotto) and format as required.

HTH


"Lofty" wrote:

I run a Pub Lotto using Excel as the spreadsheet. I have 90 rows with 7
columns. C1 is the name. C2-7 are the numbers chosen. Each week 6 numbers are
picked out. I want to highlight the numbers picked amongst the rows with red
and change the font to white. I do this by hand every week. When all 6
numbers are filled in a row I change the name to Green fill. I also have a
separate table (7x7) with 1-49 showing the numbers previously drawn which
also get filled in Red/white as they are drawn. The "this week's numbers" are
shown in a row of 6 cells. I want to automate the process using conditional
format. I get so far but it doesn't keep the formatting.
Help please!
Is it possible? or do I have to get someone to run a VB programme for me?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Keeping conditional formatting when sorting Andrea A Excel Discussion (Misc queries) 0 April 4th 06 03:00 PM
Conditional Formatting when inserting a row zahoulik Excel Worksheet Functions 2 January 7th 06 04:01 PM
Conditional Formatting Error ddate Excel Worksheet Functions 0 May 5th 05 09:00 PM
difficulty with conditional formatting Deb Excel Discussion (Misc queries) 0 March 23rd 05 07:13 PM
Conditional formatting not available in Excel BAB Excel Discussion (Misc queries) 2 January 1st 05 04:33 PM


All times are GMT +1. The time now is 10:25 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"